Re: [Sqlgrey-users] SQL Query optimization

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

Lionel Bouton wrote:

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

yes, maybe. I'll switch to 4.1 later anyway.

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

yes, i did

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



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