Hey, i've got a problem. my LEFT JOIN queries are handled about 7-6 seconds. Here is the example query:
SELECT `cars`.`cid` , `cars`.`device_id` , `cars`.`tariff` , `cars`.`car_info` , `sessions`.`updated` , `drivers` . *
FROM `cars`
LEFT JOIN `sessions` ON `sessions`.`cid` = `cars`.`cid` AND `sessions`.`updated`
IN (SELECT MAX( `updated` ) FROM `sessions` WHERE `sessions`.`cid` = `cars`.`cid`)
LEFT JOIN `drivers` ON `drivers`.`did` = `sessions`.`did`
WHERE `cars`.`company_id` =388
It joins 3 tables, all the attributes are indexes/primary keys.
This query was handled for 7 seconds and gave the response with 2 rows:
8 354776032705987 3 a:6:{s:9:"image_url";s:160:"
http://t0.gstatic.com/... 2011-03-05 17:03:15 7 9998 388 Антон Привольнов 8(495)6518428 1
9 354776032245430 3 a:6:{s:9:"image_url";s:70:"
http://avto.edelveiys.r... 2011-03-05 15:07:39 6 9999 388 Рэй Крок 8(965)4782211 0
EXPLAIN EXTENDED gives no idea why the query is so slow:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY cars ref company_id,company_id_2 company_id 4 const 2
1 PRIMARY sessions ref cid cid 4 gps.cars.cid 37
1 PRIMARY drivers eq_ref PRIMARY PRIMARY 4 gps.sessions.did 1
2 DEPENDENT SUBQUERY sessions ref cid cid 4 gps.cars.cid 374
And at last: cars table contains 6 rows, drivers contains 5 rows and sessions 1876 rows. My key_buffer_size variable is set to 1GB.