Ebay Archive chewing on MySQL server resources

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.

This entry was posted on Sunday, April 29th, 2007 at 11:50 pm and is filed under Other. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply