I though that the key_len would need to be 8 instead of 4 if the whole index on keywordid_score is used, but the missing Using filesort means index is used.

I can't just understand how reading 100 rows of indexed data can take 30second even if completely disk bound.

These are random reads, the index only helps you to avoid full table scan.

I'm no expert, but here's a couple things I would look into:

* Is your mysql db config allowing it to use maximum of the available memory?
* Does your index fit into memory?
* If you are using InnoDB, can you tolerate relaxing ACID properties on your updates? Each write uses "no OS/disk cache buffering" system calls under default config. Using InnoDB's transactions also means each update actually means ~ 3x disk writes (2x transcation log, 1x table update).
* Buy and read High Performance Mysql (2nd edition)

I have configured half available memory to mySql index as I read the other half is needed for disk buffers (and php and apache). And no the index does not fit into memory.

I ordered High Performance Mysql (2nd edition) from Amazon, it looks like a good read in this case.

I read there's differences between using text and varchar type, I'm going to investigate that furthar as it could have a major impact on case to where I read and write a 2KB text block a lot.

I just have that feeling that reading, updating and then writing 400 2KB text block should not take 60 seconds even if completely disk bound.

I changed REPLACE INTO to INSERT .. ON DUPLICATE KEY UPDATED for the text block as I read it should be faster. I didn't notice any speed difference though.

Simon

If the key length is 4, that'd be the size of a 32-bit integer. Are you sure that it isn't just using your primary key as the index?

EXPLAIN SELECT *
FROM sbs_keywordindex
ORDER BY keywordid DESC , score DESC
LIMIT 100 

Generates this

id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1    SIMPLE    sbs_keywordindex    index    NULL    keywordid    8    NULL    81448071     

key_len = 8

Anyway I found a solution to the second one of my slow queries



The speed to execute that increased by a factor of 3 by changing the table structure from

[keywordid INT PRIMARY] [sitelist TEXT]

to this

[keywordid INT PRIMARY] [sitelist VARCHAR(2800)]

That's nice

Disk IO also dropped to half, meaning I get 6 times the speed/disk IO cycles with the new structure.

Are your tables InnoDB or MyISAM?

MyISAM