I'm not sure if I have the correct indexes or if I can improve the speed of my query in MySQL?

My query has a join, and it looks like it's using two indexes which makes it more complicated. I'm not sure if I can improve on this, but I thought I'd ask.

The query produces a list of records with similar keywords the record being queried.

Here's my query.

SELECT match_keywords.padid,
       COUNT(match_keywords.word) AS matching_words
FROM   keywords current_program_keywords
       INNER JOIN keywords match_keywo开发者_JS百科rds
         ON match_keywords.word = current_program_keywords.word
WHERE  match_keywords.word IS NOT NULL
       AND current_program_keywords.padid = 25695
GROUP  BY match_keywords.padid
ORDER  BY matching_words DESC
LIMIT  0, 11  

The EXPLAIN

Word is varchar(40).

You can start by trying to remove the IS NOT NULL test, which is implicitly removed by COUNT on the field. It also looks like you would want to omit 25695 from match_keywords, otherwise 25695 (or other) would surely show up as the "best" match within your 11 row limit?

SELECT     match_keywords.padid,
           COUNT(match_keywords.word) AS matching_words
FROM       keywords current_program_keywords
INNER JOIN keywords match_keywords
        ON match_keywords.word = current_program_keywords.word
WHERE      current_program_keywords.padid = 25695
GROUP BY   match_keywords.padid
ORDER BY   matching_words DESC
LIMIT      0, 11

Next, consider how you would do it as a person.

• You would to start with a padid (25695) and retrieve all the words for that padid
• From those list of words, go back into the table again and for each matching word, get their padid's (assumed to have no duplicate on padid + word)
• group the padid's together and count them
• order the counts and return the highest 11

With your list of 3 separate single-column indexes, the first two steps (both involve only 2 columns) will always have to jump from index back to data to get the other column. Covering indexes may help here - create two composite indexes to test

create index ix_keyword_pw on keyword(padid, word);
create index ix_keyword_wp on keyword(word, padid);

With these composite indexes in place, you can remove the single-column indexes on padid and word since they are covered by these two.

Note: You always have to temper SELECT performance against

• size of indexes (the more you create the more to store)
• insert/update performance (the more indexes, the longer it takes to commit since it has to update the data, then update all indexes)

Try the following... ensure index on PadID, and one on WORD. Then, by changing the order of the SELECT WHERE qualifier should optimize on the PADID of the CURRENT keyword first, then join to the others... Exclude a join to itself. Also, since you were checking on equality on the inner join to matching keywords... if the current keyword is checked for null, it should never join to a null value, thus eliminating a compare on the MATCH keywords alias as looking at every comparison as looking for NULL...

SELECT STRAIGHT_JOIN
      match_keywords.padid,
      COUNT(*) AS matching_words 
   FROM
      keywords current_program_keywords
         INNER JOIN keywords match_keywords          
            ON match_keywords.word = current_program_keywords.word 
            and match_keywords.padid <> 25695
   WHERE  
          current_program_keywords.padid = 25695
      AND current_program_keywords.word IS NOT NULL
   GROUP BY 
      match_keywords.padid 
   ORDER BY 
      matching_words DESC 
   LIMIT
      0, 11 

You should index the following fields (check to what table corresponds)

match_keyword.padid

current_program_keywords.padid

match_keyword.words

current_program_keywords.words

Hope it helps accelerate