Need help with indexing and sorting

queries are very slow and I don't know why

[ionchannels]

I am using a table with about 7,000,000 rows each one containing a document ID, a word and a score associated with that word. There are no unique keys, but all fields have a single index. My queries are taking about 12 seconds each. Here is my query:

SELECT docid, (SUM(totalscore)) AS totalscore FROM wordlist WHERE stemmed='word1' OR stemmed='word2' GROUP BY docid ORDER BY totalscore DESC

Here is the explain:
table: wordlist
type: range
possible keys: stemmed
key: stemmed
key len: 10
ref: NULL
rows: 20099
extra: using WHERE; using temporary; using filesort

Can anyone help me with why this query is so slow? and if so, how could I speed it up? Thanks!

[txbakers]

A few questions:

Have you tried it with only one WHERE?
Have you tried it without the SUM()?

Your index looks fine for the query.

Sometimes Scalar functions with Group By take longer to execute because it has to find the row first, then store the results.

Try it in various combinations to see which works the fastest to get a benchmark.

[ionchannels]

Hi, thanks for the response. I have tried it in all the ways you mention, but still get a 10-12 second query time. I think it must be the sorting which is slow, needing temp. tables, probably swapping even... I'm not sure how to speed this one up.

[jvmills]

Are you using SQL server? If yes you can use the "View Query Execution" option in the SQL query analyser. This should tell you where your query is stalling.

You can also run the query tuning wizard which suggests suitable indexes and indexed views etc..

[FalseDawn]

Any swapping to hard disk will kill a query.
How many rows are being returned? Have you tried it without the Order By? - Often sorts require temp tables.
How much RAM does the system have? MySQL? Dedicated machine?