Re: [Sqlgrey-users] SQL Query optimization

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

Lionel Bouton wrote:

> Max Diehn wrote the following on 21.02.2005 12:56 :
> 
>> 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 4.0.22 the explain shows that I don't even need to modify the current 
> syntax to make use of the index (in the from_awl case with 1000 entries) !

In create_from_awl_table a combined primary key for all columns except 
last_seen is defined. MySQL will use this PK as the index in all queries 
where it thinks it makes sense.
In connect there is no such key, so I added the indices mentioned 
(first_seen and ip_addr).

> Does a "ANALYZE TABLE connect" change anything?
> It might be that as you want all the column contents, MySQL decides that 
> a full scan is more efficient in this case. Please do the test with a 
> "EXPLAIN DELETE FROM..." or the original SELECT but with an INTERVAL 
> where very few entries will be given back.

explain select sender_name, sender_domain, ip_addr from connect where 
first_seen > now() - interval 1 minute;

didn't use an index. I'll tell You if I find out something interesting.

Now wouldn't it be better to let an external process do the cleanup?
In that case, the smtpd doesn't need to wait for the cleanup to finish.
Hence, the duration of cleanup wouldn't be that important.

> 
> 
>> 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?
>>
>>
> 
> No it wouldn't.
> 
> Lionel.
> 
> 
> 
> -------------------------------------------------------
> SF email is sponsored by - The IT Product Guide
> Read honest & candid reviews on hundreds of IT Products from real users.
> Discover which products truly live up to the hype. Start reading now.
> http://ads.osdn.com/?ad_id=6595&alloc_id=14396&op=click
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users


-- 
Max Diehn

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