This is in reply to a message posted by Dreamhost support earlier today.
[quote]We found the problem after much looking.
A single user who had a 110MB table was performing a query similar to the following:
SELECT * FROM images WHERE imageid=‘3563’;
However, imageid was not indexed. Therefore, everytime the above query was performed (many times a second), the server had to scan through the entire 110MBs of the table to find the single record it was looking for. That was pushing the load up to 100 (where 1 is normal).
CREATE INDEX imageid_index ON images (imageid(10));
fixed the problem, and dropped the load back down to 1.
(She also indicated that her site had recently been featured on a large search engine.)
I’m ery interested in this, as my tables are growing by the day. I have indexed many of my tables before, but never figured out how to find out how much speed I have saved. I see here that you have given usage figures. How would I go about doing this for my unoptimized and optimized tables?