CODEWITHSUNDEEP
sqlmysqlindexingquery-optimization
Jules
Jules

Reputation: 7774

MySQL, puzzled why query is slow, have an index?

I'm puzzled why one of my queries is slow, I have indexes and I've just created a new one.

Yet its still been quite slow and appears in my slow log.

Heres my query...

  SELECT *, 
         COUNT( Word ) AS WordCount 
    FROM `keywords` 
   WHERE `LetterIdx` = 'A' 
GROUP BY Word 
ORDER BY Word;

Heres the query EXPLAIN'd

alt text Heres my table structure and indexes.

alt text

Upvotes: 0

Views: 202

Answers (3)

BT26
BT26

Reputation: 340

Like Richard said, you should use a compound index with LetterIdx and Word.

But I sugest you to do this query:

SELECT Word, COUNT( * ) AS WordCount
FROM `keywords`
WHERE `LetterIdx` = 'A'
GROUP BY Word

Since, GROUP BY ordered the columns, you could do this: GROUP BY Word DESC you don't have the need to use ORDER BY ...

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107806

You might find it runs a lot faster like this, if you create a composite index across (LetterIdx, Word) it may help more. This is a 2-column index instead of 2 single-column indexes

CREATE INDEX keywords_l_w on keywords(letterIdx, Word)

SELECT Word, 
     COUNT( Word ) AS WordCount 
FROM `keywords` 
WHERE `LetterIdx` = 'A' 
GROUP BY Word 
ORDER BY Word;

If my guess is correct that LetterIdx = first letter of Word, this may work even better

# First create index on Word, then 
SELECT Word, 
     COUNT( Word ) AS WordCount 
FROM `keywords` 
WHERE `Word` like 'A%' 
GROUP BY Word 
ORDER BY Word;

As for "I'm puzzled why one of my queries is slow, I have indexes and I've just created a new one."

It is because you have asked it to retrieve all columns of the record. Is that really necessary? Even if it can use the index (~ 1/26 of all data), it will still need to look up the data pages to retrieve all other columns. If you had a covering index (letteridx, word), it does not need to go back to the data.

For what it's worth, * and GROUP BY are not a good mix in a strict SQL sense.

Upvotes: 2

goat
goat

Reputation: 31854

Try a compound index on (LetterIdx, Word)

It should then be able to use the index for both that speciofc where clause, and that specific group by/order by

Upvotes: 0

Related Questions