[Repost-ish] Impossibly slow queries, Tables indexed, How can I speed it up?
- by colorfulgrayscale
Hi guys, I posted a little earlier on here at http://stackoverflow.com/questions/2656837/query-results-taking-too-long-on-200k-database-speed-up-tips asking about slow executing SQL queries. I was told to index the columns; I did. and its still slow (slow as in, i never see the results, both mysql and sqlite freeze up on query). Help would be greatly appreciated.
Here is the SQL
SELECT
equipment.`unitID` AS `equipment_unitID`,
equipment.`fleetCode` AS `equipment_fleetCode`,
equipment.type AS equipment_type,
equipment.tiremap AS equipment_tiremap,
tiremap.`TireID` AS `tiremap_TireID`,
tiremap.`WorkMap` AS `tiremap_WorkMap`,
tiremap.`Position` AS `tiremap_Position`,
tiremap.`DepthMap` AS `tiremap_DepthMap`,
tiremap.timestamp AS tiremap_timestamp,
workreference.`aMap` AS `workreference_aMap`,
workreference.`bMap` AS `workreference_bMap`,
tirework.`RO` AS `tirework_RO`,
tirework.location AS tirework_location,
tirework.mileage AS tirework_mileage,
tirework.`mechanicCode` AS `tirework_mechanicCode`,
tirework.`partNumber` AS `tirework_partNumber`,
tirework.`historyID` AS `tirework_historyID`,
tirework.workmap AS tirework_workmap,
tirework.timestamp AS tirework_timestamp
FROM equipment,
tiremap,
workreference,
tirework
WHERE equipment.tiremap = tiremap.`TireID`
AND tiremap.`WorkMap` = workreference.`aMap`
AND workreference.`bMap` = tirework.workmap
LIMIT 5
and here is the EXPLAIN for it
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE equipment ALL tiremap 14079
1 SIMPLE tiremap ref PRIMARY,WorkMap,TireID,WorkMap_2 PRIMARY 52 tire.equipment.tiremap 3
1 SIMPLE workreference ref aMap,bMap aMap 52 tire.tiremap.WorkMap 1
1 SIMPLE tirework eq_ref NewIndex1 NewIndex1 52 tire.workreference.bMap 1
