List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:April 4 2003 4:26pm
Subject:Re: MySQL index fun
View as plain text  
In the last episode (Apr 04), Dan Rossi said:
> Create a compound index on either (time, source_ip) or (source_ip,
> time).  Mysql cannot use two indexes on one table.
> 
> woah what are you saying here , that it cant have multiple indexes
> within a table ?

You can; MySQL will only use one per table per query.  For example:

> > SELECT time,source_ip,bytes FROM data.tb_ipdata_0403 WHERE 
> > (time > 1049108400 AND time < 1051786799) AND 
> > (source_ip > 3389268097 AND source_ip < 3389268099)

It's usually better to pull all the records in a particular time range
and then filter out the ones with uninteresting source_ip's (one index
lookup + one record pull => 2 I/Os per record) than it is to walk two
indexes and pull only the matching records (two index lookups + one
record pull => 3 I/Os per record).

> and what is a compound index ?

It's an index on multiple fields.  MySQL actually calls them
multiple-column indexes, Oracle calls them composite indexes.  See
http://www.mysql.com/doc/en/MySQL_indexes.html and
http://www.mysql.com/doc/en/Multiple-column_indexes.html for more info.

> say i have a table
> 
> products:
> productID
> product
> storeID <-index
> userID <-index
> 
> stores:
> storeID
> store
> 
> users:
> userID
> user
> 
> can i not have multiple indexes like that , as i usually use int keys to
> join tables together

You certainly can, and you also want indexes on storeID and userID in
the other tables also.  But if you are doing lookups in the products
table on storeID and userID at the same time, you will want a
multi-column index.  You can drop the userID index and replace it with
one on (userID,storeID), for example.  Lookups on just userID will
still be able to use that index.

-- 
	Dan Nelson
	dnelson@stripped
Thread
MySQL index funSteve Phillips4 Apr
  • Re: MySQL index funDan Nelson4 Apr
    • RE: MySQL index funDan Rossi4 Apr
      • Re: MySQL index funDan Nelson4 Apr