September 18th, 2013, 06:23 PM
Using a single, multi-column index instead of choosing one
Using EXPLAIN, I've found that my query is selecting one key to use, rather than my key that contains all keys searched on. To simplify, say my table has the following:
In a query with several joins that at some point utilizes all three of these, EXPLAIN tells me that, in this table, the "possible keys" are districtid,schoolid,and studentid, and that it's choosing as its "key" studentid. Districtid in the query is a constant set specifically.
So I add a single key for "districtid/schoolid/studentid".
ALTER TABLE `student_data`
ADD INDEX `district~schoolid~stuid` (`districtid` ASC, `schoolid` ASC, `stuid` ASC);
Running EXPLAIN again tells me the same thing, that it's just using studentid. Why would EXPLAIN not use the full INDEX instead? Could it be because it's joining from several tables? Or perhaps because this is still most efficient, being that districtid is known?
More than happy to paste the whole query but I thought this might be enough info to spark a conversation without trying to cleanse my code first.
September 18th, 2013, 07:13 PM
It depends on the table, its data (to an extent), and naturally the query. Would help to see the whole thing...