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.
>>
>>
>>
>
