2
Extremely slow indexing query
Problem reported by Colin M - September 3, 2014 at 4:03 PM
Submitted
SmarterTrack Enterprise 10.1.5283
 
In my "Errors" log I have a LOT of the following:
 
=====================
[2014-09-03 6:51:58 PM]
Agent: N/A

Indexing error: Lost connection to MySQL server during query


=====================
[2014-09-03 6:53:20 PM]
Agent: N/A

Indexing error: Lost connection to MySQL server during query


=====================
[2014-09-03 6:54:43 PM]
Agent: N/A

Indexing error: Lost connection to MySQL server during query
So almost every minute as you can see. I enabled MySQL Slow Query log and also almost every minute I have:
 
# User@Host: smartertrack[smartertrack] @ localhost [127.0.0.1]
# Query_time: 47.618165  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 53002586
SET timestamp=1409785050;
insert into st_SearchTokenStats
                (SearchTokenID, SearchFieldID, DateCalculatedUTC, WeightFactor)
                ( 
                    select R.SearchTokenID, R.SearchFieldID, '2014:09:03 22:56:42', case  when count(R.TicketID) > 163315 then 0.001  when count(R.TicketID) > 122486 then 0.01  when count(R.TicketID) > 81657 then 0.04  when count(R.TicketID) > 61243 then 0.1  when count(R.TicketID) > 40828 then 0.3  when count(R.TicketID) > 20414 then 0.4  when count(R.TicketID) > 16331 then 0.5  when count(R.TicketID) > 12248 then 0.7  when count(R.TicketID) > 8165 then 1.0  when count(R.TicketID) > 4082 then 1.5  when count(R.TicketID) > 2041 then 2.0  when count(R.TicketID) > 1020 then 4.0  when count(R.TicketID) > 408 then 6.0  when count(R.TicketID) > 81 then 10.0  else 15.0  end from st_SearchTokensInTickets R 
                    left join st_SearchTokenStats S on R.SearchTokenID = S.SearchTokenID and R.SearchFieldID = S.SearchFieldID
                    where R.SearchTokenID > 0 and (S.SearchTokenID is null or S.SearchFieldID is null) 
                    group by R.SearchTokenID, R.SearchFieldID
                );
The problem is obviously the 53,002,586 rows examined! No amount of hardware upgrades is going to alleviate this issue and it will only get worse.. I have a 47 second query occurring every 60 seconds, and it is getting longer over time.. So before long I'm going to have a really major issue...
 
Please optimize this query somehow to use an index, and soon!

Reply to Thread