I think I've isolated a problem that has been affecting many of my queries lately. And would like some help to figure out a solution for this.
Ok so my findings are that a normal query that runs very fast using like a couple of rows can actually use indexes improperly when used in a subquery which is based on values from the main query.
Lets take an example:
DROP TEMPORARY TABLE IF EXISTS Person; DROP TEMPORARY TABLE IF EXISTS CofeeBreaks; CREATE TEMPORARY TABLE IF NOT EXISTS Person ( `person_id` INT(11) AUTO_INCREMENT, `age` INT, `lastCofee` DATETIME, KEY `idkey` (`person_id`) USING BTREE, KEY `datekey` (`lastCofee`) USING BTREE ) ENGINE = MEMORY; CREATE TEMPORARY TABLE IF NOT EXISTS CofeeBreaks ( `id` INT(11) AUTO_INCREMENT, `cofeeBreakStart` DATETIME, `cofeeBreakEnd` DATETIME, KEY `brekIdKey`(`id`) USING BTREE ) ENGINE = MEMORY; INSERT INTO Person (age, lastCofee) VALUES (24, '2013-03-27 14:45:34'); INSERT INTO Person (age, lastCofee) VALUES (34, '2013-03-27 14:46:38'); INSERT INTO Person (age, lastCofee) VALUES (26, '2013-03-27 15:25:24'); INSERT INTO Person (age, lastCofee) VALUES (28, '2013-03-27 16:33:54'); INSERT INTO Person (age, lastCofee) VALUES (46, '2013-03-27 17:11:03'); INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 15:11:03', '2013-03-27 17:25:24'); INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 14:45:34', '2013-03-27 15:25:24'); INSERT INTO CofeeBreaks (cofeeBreakStart, cofeeBreakEnd) VALUES ('2013-03-27 17:11:03', '2013-03-27 17:11:03'); SELECT * FROM Person WHERE lastCofee BETWEEN '2013-03-27 15:11:03' AND '2013-03-27 17:11:03'; SELECT *, (SELECT AVG(Person.age) FROM Person WHERE Person.lastCofee BETWEEN CofeeBreaks.cofeeBreakStart AND CofeeBreaks.cofeeBreakEnd) AS averageAge FROM CofeeBreaks;
So the explain results for the first select are as follow:
1 SIMPLE Person range datekey datekey 9 1 Using where
But the second query doesn't use the index properly in the subquery and analyses more rows than necessary:
id select_type table type possible_keys key key_len ref rows 1 PRIMARY CofeeBreaks ALL 3 2 DEPENDENT SUBQUERY Person ALL datekey 5
As we can see the subquery needs to analyse all rows in the person table when none of the cofeebreaks ranges surrounds all of the 5 persons.
The way I've been fixing the performance issues in a very busy database is by doing a select into a temporary table and than looping the rows of that table and updating it with the aggregates that i need. This is obviously ugly code and shouldn't be built that way. But I really haven't found a way to optimize queries for this kind of subqueries and I've tried a lot of possible ways to do this without success on the optimization.
Thanks in advance for any inputs.