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