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.