I received an email from Dreamhost this morning with regards to the Ebay Archive database. It seems that the queries that it was performing wasn’t as optimised as I thought.
# Query_time: 0 Rows_examined: 156795 use ebayarchive; SELECT item_number FROM items_pending WHERE item_number = 290108127963;
150k+ rows just for a search?
Sounds like it’s not even using the index I had…
I looked up how to find useful information about queries on google and it says I should put EXPLAIN before the query, so I did.
SQL query: EXPLAIN SELECT item_number FROM items_pending WHERE item_number = 200103800946; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE items_pending index item_number item_number 16 NULL 158537 Using where; Using index
But it was using the index… hmm, how interesting. How about we try to put the number in quotes?
SQL query: EXPLAIN SELECT item_number FROM items_pending WHERE item_number = '200103800946'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE items_pending ref item_number item_number 16 const 1 Using where; Using index
Ah ha! What a difference that makes… !
As for the other query that was taking up alot of resources:
EXPLAIN SELECT item_number, category, item_name, item_url, item_end_time FROM items_pending ORDER BY item_end_time LIMIT 0 , 20; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE items_pending index NULL item_end_time 4 NULL 158537
Doesn’t look promising. It seems like ORDER BY is causing the query not to perform as best it can. So we’ll have to rewrite this query in a different way and also cache the result as the file that uses this query processes each individual item.
Something like this works fine:
EXPLAIN SELECT item_number, category, item_name, item_url, item_end_time FROM items_pending_disabled WHERE item_end_time <= 1177143110 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE items_pending_disabled range item_end_time item_end_time 4 NULL 1714 Using where
We’ll have to cache all the rows and then increment the number by say 200-500 and repeat the query. Better a few rows than thousands. Now I’ve just got to wait for Dreamhost to hopefully re-enable the table they disabled.