Performance of two similar Selects
Posted by:
Jay Klein
Date: October 12, 2006 06:34PM
Hello,
I have a table with 5 columns, 2 indices other than the primary key, and 100 million rows. The program accessing this table performs only SELECTs, and a lot of them in a sequential manner, primarily of two types: one matching on one index, the other on the other index. One type executes in under a tenth of a second, while the other takes 3-30 seconds. So I'm trying to figure out the difference between them. Here's the EXPLAIN output for each:
The slow one:
mysql> EXPLAIN SELECT * FROM ratings WHERE user = 2361492 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ratings
type: ref
possible_keys: user
key: user
key_len: 4
ref: const
rows: 131
Extra:
1 row in set (0.00 sec)
The fast one:
mysql> EXPLAIN SELECT * FROM ratings WHERE movie_id = 2000 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ratings
type: ref
possible_keys: movie_id
key: movie_id
key_len: 4
ref: const
rows: 31161
Extra:
1 row in set (0.27 sec)
Despite the much greater number of rows in this particular example, the second query executed in 0.79 seconds, and the first one in 2.45. Generally the number of rows returned/examined is similar between the two types, between a few hundred and a few thousand. In the slow query log, I saw that for the slow queries the number of rows examined was the same as the number returned.
The only differences I can see between the two indexes/queries are:
1. movie_id is a 6 digit int ranging from 0 to 18000 or so with no gaps, while 'user' is a 7 digit int with gaps.
2. Not really a difference, but I was surprised to see that for both columns the 'key_len' is shorter than the column length.
Could either of these things produce this behavior? It will be quite time-consuming to drop an index and recreate it with different settings, so I want to have an idea that it might work before I try it. Thanks a lot for any help you can offer.
This is MySQL 5.0.21 on Mac OS X with 512MB RAM.
Subject
Views
Written By
Posted
Performance of two similar Selects
2935
October 12, 2006 06:34PM
1958
October 17, 2006 02:07PM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.