[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Indexes
From:       Danny Arseneau <danny () mamma ! com>
Date:       1999-06-30 19:23:38
[Download RAW message or body]

Hello everyone, I've been doing a little bit of experimentation with
indexes and would appreciate it if someone could clarify something for
me.

I have the following table:

TABLE TEST
customer_id int,
banner_id int default 0 not null,
date_time timestamp(14) not null,
cb_partner char(30) default '' not null,
key idx1 (banner_id),
key_idx2 (date_time),
key_idx3 (cb_partner)

The table contains approximately 2 million rows of data.

When the following select is performed it takes an average of 3 seconds
to complete:

select count(*) from table test where banner_id = 884937432 and
cb_partner = 'partner1' and date_time < 19990630000000';

We felt that this was taking too long. So we decided to re-arranges the
indexes a little differently by replacing the three seperate indexes
with a single one as follows:

key idx1 (banner_id, cb_partner, date_time)

What we discovered is that this index arrangement results in about the
same time to run the above query. We really expected that this new index
arrangement would be quicker. Can anybody explain this?

We are running Mysql version 3.22.19 on a Linux system with kernel
version 2.0.34.

Thank you very much and have a nice day everyone.


-- 

Danny Arseneau
Chief Technology Officer
Mamma Systems Inc.

---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread6283@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail mysql-unsubscribe@lists.mysql.com instead.

[prev in list] [next in list] [prev in thread] [next in thread]