#1
  1. hiding my <b> from ur <strong>
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2004
    Posts
    959
    Rep Power
    181

    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:

    districtid (BIGINT)
    schoolid (BIGINT)
    studentid (VARCHAR100)

    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.

    Thanks all.
    ****
    Enjoy my post? Drop some props by hitting the scales button up top. JBL

    Website Design in Los Angeles and Washington, DC by PoweredPages.com
  2. #2
  3. Did you steal it?
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    13,995
    Rep Power
    9397
    It depends on the table, its data (to an extent), and naturally the query. Would help to see the whole thing...

IMN logo majestic logo threadwatch logo seochat tools logo