When ever you use an index on multiple columns remember that mysql uses the concept of leftmost prefix. I'll explain with an example from one of your key make-ups below
If you have dest_ip and source_ip in your select statement idx_time_dest_ip_source_ip would not work, because time is your leftmost prefix in the key list. If you had just - time, dest_ip then that key would work. But what you have below should use the idx_time_dest_ip_source_ip key right? No it wouldn't. Indeed it has source_ip, dest_ip and time but notice that OR between source_ip and dest_ip. That's preventing the key lookups. You could do something like force the optimizer to use a key with the command USE_KEY (or something like that), or change the query around. -----Original Message----- From: Steve Phillips [mailto:[EMAIL PROTECTED] Sent: Friday, March 28, 2003 1:08 PM To: [EMAIL PROTECTED] Subject: optimal selects and indexes ? Hey there, I have a database where i am collecting netflow records, the number of entries in a table after a day or so reaching toward the millions of rows I am trying to pull hourly reports, summarizing the byte count's for the hour previous and writing this out to a csv file My query currently looks something like.. SELECT sum(bytes) FROM tb_ipdata_0303 WHERE (source_ip > 3329275903 AND source_ip < 3329276160) OR (dest_ip > 3329275903 AND dest_ip < 3329276160) AND (time > 1048802400 AND time < 1048805999); It appears that with a explain SELECT statement that no indexes are being used for this query. As I'm a bit of a newbie at indexing, I have currently indexed PRIMARY KEY (id), KEY idx_source_ip (source_ip), KEY idx_dest_ip (dest_ip), KEY idx_time_source_ip (time,source_ip), KEY idx_time_dest_ip (time,dest_ip), KEY idx_time_dest_ip_source_ip (time,dest_ip,source_ip), KEY idx_time (time) yet the explain still claims that no indexes are being consulted for my query, why would this be ? is there a better way to construct the select so it uses indexes ? or have i created my indexes incorrectly ? any help would be appreciated :-) -- Steve Phillips Professional Slacker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
