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.
[code]# Query_time: 0 Rows_examined: 156795
use ebayarchive;
SELECT item_number
FROM items_pending
WHERE item_number = 290108127963;[/code]
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.
[code]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[/code]
But it was using the index… hmm, how interesting. How about we try to put the number in quotes?
[code]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[/code]
Ah ha! What a difference that makes… !
As for the other query that was taking up alot of resources:
[code]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[/code]
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:
[code]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[/code]
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.