January 10th, 2017, 05:49 AM
Help to optimise slow query
I have the following table setup:
order_id prim auto Inc
name varchar 255
status tiny int
id prim auto Inc
Order_id int fk orders.order_I'd
field_key varchar 255 normal index
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.
January 10th, 2017, 08:05 AM
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
January 10th, 2017, 09:01 AM
Of course my mistake:
Originally Posted by r937
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;
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
January 10th, 2017, 02:45 PM
and if performance doesn't improve, examine your EXPLAIN output
CREATE INDEX try_this
ON order_data ( order_id
, field_value )
January 10th, 2017, 03:31 PM
The MyISAM storage engine doesn't support foreign keys. You need to change to the InnoDB storage engine.
January 10th, 2017, 03:45 PM
where did this come from? he's not creating foreign keys
Originally Posted by FishMonger
January 10th, 2017, 03:55 PM
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.
Originally Posted by r937
January 10th, 2017, 04:01 PM
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.