Re: [Sqlgrey-users] SQL Query optimization

[Lionel B. <lio...@bo...>]

Max Diehn wrote the following on 21.02.2005 16:01 :

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


With a WHERE clause involving only the timestamp, only the index on the 
timestamp can be used. So as the query I did on from_awl involved a 
timestamp and where using the same syntax for the WHERE clause I suppos 
MySQL query planner should use the same strategy.
Maybe 4.0.22 is simply clever than your version.

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


You did ANALYZE TABLE before the explain, didn't you?

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

This is planned: my current idea is to make SQLgrey fork a process which 
will connect to the database separately for the cleaning. I don't know 
yet if its better to make it a standalone process with its own 
scheduling or more simply fork a short lived process *just after* doing 
the queries involved to answer a request.

Lionel.