ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Zoran Avtarovski <zo...@sparecreative.com>
Subject Re: OT: COUNT query speed issue
Date Wed, 22 Jun 2005 00:06:27 GMT
Thanks Philippe,

I got past that. And in the end I followed a suggestion by Nils and read the
MySQL cookbook by O'Reilly (it's amazing how much a little effort pays off)
to change my Query to:

SELECT DATE_FORMAT(log_time,'%M %d') AS date, COUNT(*) AS count  


FROM  log    

WHERE  remote_id = #remoteId# AND log_time BETWEEN #lowDate# AND #highDate#
    
GROUP BY date


This returns a list of dates and associated counts in one SQL query. This
combined with the indexing has increased speed well beyond my wildest
imagination.

Zoran

>> The log_time column is indexed, but I couldn't work out how to index two
>> columns in mysql.
> 
> Indexing two (or more) columns in MySQL is the same as indexing one:
> 
> CREATE TABLE a_table(
>    remote_id INTEGER UNSIGNED NOT NULL,
>    log_time DATE NOT NULL,
>    INDEX remote_time(remote_id, log_time)
> )
> 
> Simply comma-seperate the column names in your INDEX statement... This
> is the MySQL >4.0 syntax. I don't know about previous versions.
> 
> Cheers,
> Philippe
> 
> Zoran Avtarovski wrote:
>>> Do things get faster if you have the database index remote_id and
>>> log_time?
>>> 
>> 
>> The log_time column is indexed, but I couldn't work out how to index two
>> columns in mysql.
>> 
>> Z.
>> 
>> 
>> 
> 



Mime
View raw message