List:General Discussion« Previous MessageNext Message »
From:Andrey Dmitriev Date:February 7 2012 8:13pm
Subject:Best way to tune substr,min,max query
View as plain text  
I have a query that I need to tune.
Basically, substr a text, and select first and last entry.
The table is currently a few million rows big. Index is on FromHost (text
field) and ReceivedAt (index field)
Is the best way to optimize my query.
1) create an index on substr() and the two date columns
2) create some triggering mechanism to just populate this data in a report
3) create table as select

I would strongly prefer 1, or something similar/simple so that I wouldn't
have to maintain triggers in case they break. I don't like 3 because it's
heavy.

Thanks,

select
substr(rsyslog.SystemEvents.FromHost,1,if((locate(_latin1'.',rsyslog.SystemEvents.FromHost)
> 0),(locate(_latin1'.',rsyslog.SystemEvents.FromHost) -
1),length(rsyslog.SystemEvents.FromHost))) AS hostname,
max(rsyslog.SystemEvents.DeviceReportedTime) AS first_syslog_entry,
min(rsyslog.SystemEvents.DeviceReportedTime) AS last_syslog_entry
from rsyslog.SystemEvents
group by
substr(rsyslog.SystemEvents.FromHost,1,if((locate(_latin1'.',rsyslog.SystemEvents.FromHost)
> 0),(locate(_latin1'.',rsyslog.SystemEvents.FromHost) -
1),length(rsyslog.SystemEvents.FromHost)))

Thread
Best way to tune substr,min,max queryAndrey Dmitriev7 Feb