Using the excellent New Relic, I've noticed thousands of queries in the format SELECT t.tid, t . * FROM term_data t WHERE t.name = 'SOMEWORDHERE' so I wanted to look if it's optimized.

I noticed there was no index on the 'name' column. Not sure whether it is needed. However, I ran it through EXPLAIN without the index and with it, and MySQL clearly uses the index when it is available!

See the attached screenshots. If you think this is a valid find, maybe it is worth adding it to DB Tuner.

CommentFileSizeAuthor
term_data_with_name_index.JPG22.18 KBvacilando
term_data_without_name_index.JPG22.41 KBvacilando

Comments

mikeytown2’s picture

mikeytown2 commented
Title: Index name in term_data » dbtuner_empty_index missing the term_data table.
Category: task » bug

This index is actually already in the code but it appears that the dbtuner_empty_index function is messing up in this case.

mikeytown2’s picture

mikeytown2 commented

Looking over the code and name is already apart of a multi-index

term_data
    [PRIMARY] => Array
        (
            [0] => tid
        )
    [taxonomy_tree] => Array
        (
            [0] => vid
            [1] => weight
            [2] => name
        )
    [vid_name] => Array
        (
            [0] => vid
            [1] => name
        )
)

      // Index exists but is apart of a multi index;
      // these are not supported at this time
      elseif ($match) {
        return FALSE;
      }

So this might take some time...

vacilando’s picture

vacilando commented

Indeed, it's a part of indices vid_name and taxonomy_tree. However, these are not used by MySQL for the very frequent query cited above.

Could you explain what you mean by "this might take some time"? Is it because you focus on columns with no indices at all first and leave cases like this for later? If so, why?

mikeytown2’s picture

mikeytown2 commented

Look at line 680
http://drupalcode.org/project/dbtuner.git/blob/refs/heads/6.x-1.x:/dbtun...
I already have the name index on the table term_data in the code. I need to rework this module to take into account more complex data structures.