Reducing search time in a 4 million-record table

[B]

Hello All,

This is my first time using this list, but hopefully I got the right one
for the question I need to ask :).

I have a table which has about 4 million records. When I do a search
(as I will explain below) it takes about 1.35 secs to get me back what I
am looking for. Since I am doing multiple types of these searches, the
total time goes in minutes, therefore, I am trying to see if I can get
any help in reducing this time from your suggestions.

There are 25 columns (of mixed data types) in this table, but my search
usually involves just the first 3 columns which are all integer values.

Let's say the first 3 columns are A,B and C respectively. My search
really needs to get all the As and look for the B's and some range of
Cs. The rows that have these values is what should be returned.

I have indexed A, so when I do a search with just using A (and not with
B and a C range), the search is done in 0.03 secs. If I include B
and/or C to that search, the search is done in 1.35 secs. I also tried
indexing B and then also C, but the search still took 1.35 secs.

The search is normally done as below (shown as an example):
select * from tab1 where A = 90 AND B = 37 AND C BETWEEN 10 AND 20;

As I said before, if the search only involved A, the search is done in
0.03 secs. And if B is also and indexed and the search is done using B
only, the search is done in about 0.05 secs. However, both A and B take
over 1 sec.

Is there anyway I can make this faster? Basically, I think, mysql
should first get all the As (since it seems that results faster) and
then look for B and then the C range. Should I change the indexing?
Should I change the select query? How?

Thanks for any help in advance,
Stan

[Bill Karwin]

B wrote:

Is there anyway I can make this faster? Basically, I think, mysql
should first get all the As (since it seems that results faster) and
then look for B and then the C range. Should I change the indexing?
Should I change the select query? How?

Generally, MySQL can use only one index per table in a given query
(there is an improvement in MySQL 5.0 such that multiple indexes can be
merged, but only for certain types of conditions). So it's not
surprising that adding the indexes on B and C didn't improve the
performance, if the index on A was already being used.

You could try creating a compound index on all three columns: A, B, C.
Not a unique key or primary key, if that's not appropriate for the
table, but just an index. I'm not certain this will improve it for your
case, but it's possible.

Make sure your cache is large enough to hold the entire index, so the
query doesn't have to go to disk during the search. An index on 4
million integers is at least 16MB, which is probably larger than the
default cache sizes. Change the cache sizes by setting options in your
my.cnf (or my.ini) file.

If you use MyISAM tables, this is controlled with the key_buffer_size
server parameter. See
http://dev.mysql.com/doc/refman/5.0/...variables.html

If you use InnoDB tables, this is controlled with the
innodb_buffer_pool_size. See
http://dev.mysql.com/doc/refman/5.0/...arameters.html

There might be a way to combine your multiple queries into fewer
queries, or even one query. But I don't know the specifics of your
table structure or the queries you're running, since you've given a
simplified example.

Regards,
Bill K.