List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 28 2003 10:27pm
Subject:Re: optimal selects and indexes ?
View as plain text  
At 10:08 -1100 3/28/03, Steve Phillips wrote:
>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 

The use of OR in your query prevents the optimizer from using indexes.

>any help would be appreciated :-)
>Steve Phillips
>Professional Slacker

Paul DuBois
sql, query
optimal selects and indexes ?Steve Phillips28 Mar
  • Re: optimal selects and indexes ?Paul DuBois28 Mar
  • RE: optimal selects and indexes ?Dathan Vance Pattishall28 Mar
  • Re: optimal selects and indexes ?Keith C. Ivey28 Mar