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 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
