Strange SELECT speed problem using mysql 4.0.xx

[Sven Reifegerste]

Hi,

i have a table with INT columns id,key,b1,b2,c1,c2, having 1.500.000
rows. 'key' and 'id' are indexed (Kardinality 385381) and id
(Kardinality 1541525).

Performing a

SELECT * FROM table WHERE key IN (10,11,12) OR key BETWEEN 20000 AND
28000 AND b1<4500000 AND b2>3954545 AND c1<4543554 AND c2>4400000
ORDER BY id LIMIT 0,100000

on that table (linux) results in ca. 100 lines within 70..80 ms (like
expected), but sometimes it takes 10 sec(!) and more although an
EXPLAIN SELECT tells me that the index of 'key' will be used:

table type possible_keys key key_len ref rows Extra
table range key key 5 NULL 11393 Using where;
Using filesort

Doing the SELECT with changing one of the IN()-values results
afterwards is quite fast SELECT's, seems that the caching of the
underlying file system is responsible for the faster speed.
2 Questions:

1. Why are there so big time differencies SELECT's like above
_although_ the 'key' index is used ?

2. How can one optimize mysql SELECTS for the table above? Are there
optimizations in mysql´s ini file for large tables (small tables dont
show such speed diefferencies) ?

thank you. Sven

[Aggro]

Sven Reifegerste wrote:

1. Why are there so big time differencies SELECT's like above
_although_ the 'key' index is used ?
I'm assuming the key is not unique value and quite many rows match given
key values. If there are many rows, caching the results will give speed
differences when doing queries.
2. How can one optimize mysql SELECTS for the table above? Are there
optimizations in mysql´s ini file for large tables (small tables dont
show such speed diefferencies) ?

You would need more indexes in your columns to help the key-index to
find the correct rows. But this is all only guessing, since I don't know
how many rows would this query return:
select count(*) from table WHERE key IN (10,11,12);

The main idea how ever is to try to minimize the amount of rows that
non-indexed column keys need to be compared with.

Adding indexes will how ever increase the disk space loss and slow the
insert and propably update queries also by small amount. (Only important
if you need to insert thousands of rows fast into the database.)

[Sven Reifegerste]

> > 1. Why are there so big time differencies SELECT's like above

_although_ the 'key' index is used ?
I'm assuming the key is not unique value and quite many rows match given
key values.

Right, the key is not unique. The result when using the key only (IN
and BETWEEN clauses) is in this example about 20 times bigger
(30000-40000 rows), and _normally_ it takes about 100-200ms, but
sometimes magnitudes more (10..20 seconds).
The main idea how ever is to try to minimize the amount of rows that
non-indexed column keys need to be compared with.

Sounds good. But even 30000*4 comparisons cant last 10..20 seconds??
While experimenting, I got an idea what the reason for those slow
SELECTS could be: The idea of the one-dimensional 'key' column is that
it encodes relevant information of b1,b2,c1,c2 into only one number.
So, only the 'key' column has to be used for a rough elimination of
non-potential rows (which saves alot of time for searching in 99% of
all cases), followed by fine-filtering using b1,b2,c1,c2. I realized
that SELECTs without using the 'key' longs 10..20 ms. This is the time
duration that the SELECT in question took about.

Thus, it seems that mysql decides sometimes for _not_making_use_ of
the index to filter relevant rows _although_ it would make sense. Thus
I'm really suspicious about the result of an EXPLAIN SELECT telling me
that the 'key' index is used. And if it would be used, then surely in
a non-senseful order. Its also quite strange that I cannot reproduce
any of these long-lasting SELECTs, neither by modifying some
parameters to circumvent the query cache nor by restarting mysql and
flushing tables.

Any ideas how to force mysql to use the key column?
Thank your for previous and upcoming responses!
Sven

[Aggro]

Sven Reifegerste wrote:

Right, the key is not unique. The result when using the key only (IN
and BETWEEN clauses) is in this example about 20 times bigger
(30000-40000 rows), and _normally_ it takes about 100-200ms, but
sometimes magnitudes more (10..20 seconds).
That sounds really strange. How did you create the index column? I've
always used this syntax:

create index indexname on tablename( columnname [,2nd_columnname,...] );

because I once noticed that when I had set a column to be a key or
something, it didn't create an index for it, or if it did, it wasn't
really fast. After creating the index that way, queries worked faster so
I started using that without bothering to investigate it any further.
Sounds good. But even 30000*4 comparisons cant last 10..20 seconds??
I agree, it shouldn't, so it is either checking all or some of the
columns that it shouldn't.
Any ideas how to force mysql to use the key column?

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;

http://dev.mysql.com/doc/mysql/en/Optimizer_Issues.html