Not the answer you need?
Register and ask your own question!

Mysql join sometimes slow sometimes fast

jorje29jorje29 EntrantCurrent User Role Beginner
Hello, I can't understand why the following join query is slow ( > 100 sec !! ) after inactivity and fast ( < 1 sec ) when apache is warmed up. I have index playerid in zztemp_min and index playerid1_vsplayerid1_date in final_log. Query cache is disabled. The explain output shows me :






1
SIMPLE
t1
index
playerid
playerid
2
NULL
1



1
SIMPLE
t2
ref
playerid1_vsplayerid1_date
playerid1_vsplayerid1_date
2
db.t1.playerid
1145
Using where




SELECT t1.playerid, sum(t2.pts), sum(t2.reb), sum(t2.turn), sum(t2.dreb), sum(t2.oreb), sum(t2.fgm), sum(t2.fga), sum(t2.fgm3), sum(t2.fga3), sum(t2.ftm), sum(t2.fta)
FROM db.zztemp_min t1 FORCE INDEX (playerid)
STRAIGHT_JOIN db.final_log t2 FORCE INDEX (playerid1_vsplayerid1_date)
ON t2.playerid1 = t1.playerid && (t2.vsplayerid1 = 1 || t2.vsplayerid1 = 2 || t2.vsplayerid1 = 4 || t2.vsplayerid1 = 5 || t2.vsplayerid1 = 6 || t2.vsplayerid1 = 7 || t2.vsplayerid1 = 10) && t2.date >= t1.datelimit && t2.date <= '2016-04-13'
GROUP BY t1.playerid

Any recommendation what the problem might be ?

Thank you
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.