Re: [Sqlgrey-users] SQL Query optimization

[Max D. <Max...@lr...>]

Lionel Bouton wrote:

> Michel Bouissou wrote the following on 19.02.2005 10:13 :
> 
>> Hi there,
>>
>> (I'm using PostgreSQL, so maybe your mileage may vary if you use 
>> another SQL DB system).
>>
>> I tried to optimize DB cleanup time by creating indexes on the 
>> last_seen column (domain_awl and from_awl) and on the first_seen 
>> column (connect), so the cleanup process can use these indexes to find 
>> entries older or newer than a given value rather than having to 
>> explore sequentially the complete tables.
>>
>> This can make a big difference for large sites having tens of 
>> thousands entries in their tables...
>>
>> However, usingt the PostgreSQL "explain" statement, I've been 
>> surprised to notice that the indexes I had created where *NOT* used 
>> when performing the kind of queries that SQLgrey performs, with a 
>> WHERE clause built such as in:
>>
>> select * from from_awl where last_seen < now() - INTERVAL '30 DAY';
>>
>> However, the indexes would be used if the WHERE clause used a 
>> constant, such as in:
>>
>> select * from from_awl where last_seen < '2005-01-19 08:43:09';
>>
>> It seems that what prevents the index from being used, is having the 
>> "now() - INTERVAL '65 DAY'" calculation performed as an expression in 
>> the WHERE clause comparison.
>>  

using first_seen and ip_addr as (separate) indices in connect (using 
MySQL 3.23.55), I found, that MySQL uses index for

	explain select count(*) from connect where first_seen <
	'2005-02-21 08:43:09';

as well as for

	explain select count(*) from connect where first_seen < now() -
	interval 12 hour;

but not for

	explain select * from connect where first_seen < now() -
	interval 12 hour;

or

	explain select * from connect where first_seen < '2005-02-21
	08:43:09';

(or f.e.
	explain select sender_name, sender_domain, ip_addr from connect
	where first_seen <  '2005-02-19 08:43:09';
)

So in case of MySQL 3.23.55 it doesn't seem to be a matter of using 
now() or not.

On the other hand, using a compound small index like

	alter table connect add index (sender_name(8),
	sender_domain(8), rcpt(8));

wouldn't help on this kind of query, right?


-- 
Max Diehn

-------------------------------------------------
Leibniz-Rechenzentrum   !   <mailto:di...@lr...>
Barer Str. 21           !
80333 Muenchen, Germany !   Tel: +49 89 289-27823