#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    2
    Rep Power
    0

    Help to optimise slow query


    Hi

    I have the following table setup:

    Orders
    order_id prim auto Inc
    name varchar 255
    status tiny int
    created timestamp

    Order_data
    id prim auto Inc
    Order_id int fk orders.order_I'd
    field_key varchar 255 normal index
    field_value text


    The purpose of the table is a raw import of scaned pdfs. So each file create an order and 127 keys and values in order_data.

    At present time I have roughly 70 000 records in order_data and I need to be able to filter orders based on values in order_data.
    So I have a basic join between order an order_data then I have a where statement like this. order_data.field_key like 'pick_up_%' and field_value Between '10:00' AND '15:00' group by orders.order_id.

    It works but it takes roughly about 10 secs to execute. I know that the like statement is bad but I need to scan all weekday keys. There are a pick_up_monday Thursday and so on.
    The main goal of the query is to find orders based on the time interval so field value is a string containing times like 10:00 and so on.

    I would love any input to make this query faster.

    Thanks
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,662
    Rep Power
    4288
    first of all, the "best practice" method of showing your tables is to post the result of the SHOW CREATE TABLE command for each table, which will also include any indexes that have been declared

    ... and that information is crucial to understanding performance issues
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2017
    Posts
    2
    Rep Power
    0
    Originally Posted by r937
    first of all, the "best practice" method of showing your tables is to post the result of the SHOW CREATE TABLE command for each table, which will also include any indexes that have been declared

    ... and that information is crucial to understanding performance issues
    Of course my mistake:

    orders

    CREATE TABLE `orders` (
    `order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `status` int(10) unsigned NOT NULL,
    `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `created` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`order_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=540 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    SET FOREIGN_KEY_CHECKS=1;

    order_data

    CREATE TABLE `order_data` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `order_id` int(10) unsigned NOT NULL,
    `field_key` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `field_value` text COLLATE utf8_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    KEY `field_key` (`field_key`)
    ) ENGINE=MyISAM AUTO_INCREMENT=64343 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,662
    Rep Power
    4288
    Code:
    CREATE INDEX try_this
        ON order_data ( order_id
                      , field_key
                      , field_value )
    and if performance doesn't improve, examine your EXPLAIN output
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Apr 2009
    Posts
    2,229
    Rep Power
    1231
    The MyISAM storage engine doesn't support foreign keys. You need to change to the InnoDB storage engine.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,662
    Rep Power
    4288
    Originally Posted by FishMonger
    The MyISAM storage engine doesn't support foreign keys. You need to change to the InnoDB storage engine.
    where did this come from? he's not creating foreign keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Apr 2009
    Posts
    2,229
    Rep Power
    1231
    Originally Posted by r937
    where did this come from? he's not creating foreign keys
    First, it was indicated in the opening post: "Order_id int fk orders.order_I'd" but the output of the "show create table" didn't reflect the same, however it did include "SET FOREIGN_KEY_CHECKS=1;". Setting that variable doesn't make sense for a MyISAM table.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Apr 2009
    Posts
    2,229
    Rep Power
    1231
    BTW, these tables should be linked via foreign key rather than relying on remembering to always enforce the linking via the php (or other language) code.

IMN logo majestic logo threadwatch logo seochat tools logo