Seemingly erratic behavior
Here is the bear of a query behind this question:
SELECT
code,
count(*) as 'count'
FROM (
SELECT distinct p.promo_code, submitid,
IF(ISNULL(p.promo_code), IF(h.account_number = c2.account_number, 12,9),
IF(c.full_name = CONCAT(h.firstname,' ',h.lastname),
IF(FROM_UNIXTIME(p.start_date) > concat(CURDATE( ) , ' 23:59:59'),
1, /*Match Name, Future Start Date*/
IF(p.`status` = 'REDEEMED',
2, /*Match Name, Redeemed*/
IF(FROM_UNIXTIME(p.end_date) < concat(CURDATE( ) , ' 23:59:59'),
IF(ADDDATE(FROM_UNIXTIME(p.end_date), INTERVAL 3 DAY) >= concat(CURDATE( ) , ' 23:59:59'),
10, /*Match Name, Bufferd*/
5), /*Match Name, Expired*/
6) /*Match Name, Active*/
)
),
IF(FROM_UNIXTIME(p.start_date) > concat(CURDATE( ) , ' 23:59:59'),
3, /*Name Different, Future Start Date*/
IF(p.`status` = 'REDEEMED',
4, /*Name Different, Redeemed*/
IF(FROM_UNIXTIME(p.end_date) < concat(CURDATE( ) , ' 23:59:59'),
IF(ADDDATE(FROM_UNIXTIME(p.end_date), INTERVAL 3 DAY) >= concat(CURDATE( ) , ' 23:59:59'),
11, /*Name Different, Buffered*/
7), /*Name Different, Expired*/
8) /*Name Different, Active*/
)
)
)
) AS 'code'
FROM helpdesk h
LEFT JOIN promo_codes p
ON h.promo_code = p.promo_code
LEFT JOIN customer_promocode cp
ON p.promo_code = cp.promo_code
LEFT JOIN customers c
ON cp.customer_number = c.customer_number
LEFT JOIN customers c2
ON h.account_number = c2.account_number
LEFT OUTER JOIN redeem_queue r
ON p.promo_code = r.promo_code
WHERE
h.help_status = 0
AND h.in_view_by = ''
AND (p.start_date >= UNIX_TIMESTAMP(SUBDATE(CURDATE(), INTERVAL 1 YEAR))
OR p.start_date IS NULL)
AND (ISNULL(p.promo_code) OR ISNULL(r.promo_code))
) x
GROUP BY x.code
The query runs in 0.438 sec. when run through the HeidiSQL MySQl tool.
A quick run of the querythrough the Percona Query Advisor resulted in "You're awesome! We couldn't find any problems with this query."
Which I don't believe, but at least seems to point to the query NOT being awful.
Anyway, this query is part of an online system. What I am experiencing is this:
Initial log on - query runs fine and page loads
Navigate through some other pages (each of which run different queries), after each of those pages loads, navigate back to the page that runs this query - query runs fine and page loads
Navigate to a page, make request causing a different query to run, taking about 35-40 sec. - not good,but it is doing many, many calculations and it does not time out or hang. NOW I navigate back to the page that runs the query in question and it hangs, eventually loading the page but with no data.
I check processlist and sure enough there it is, just taunting me. (I can kill it and all is fine, but obviously I can't do that every time a user runs into this)
I navigate away from the page - now what ever query runs takes much longer than it had previously, and may or may not cause a 504 Gateway Time-out
After the page loads, or I get the 504 I can navigate back to the page with the query in question and it runs and loads in the 0.4 seconds again.
I navigate to the page that just caused the 504, and it too returns in under 0.4 seconds.
I do not understand this erratic behavior.
If any of this makes sense, please help. If you need more data, please help me to obtain it for you - tell me the steps to take
This is InnoDB in a clustered environment. I can see the following settings for cache
"Variable_name" "Value"
"have_query_cache" "YES"
"query_cache_limit" "1048576"
"query_cache_min_res_unit" "4096"
"query_cache_size" "0"
"query_cache_strip_comments" "OFF"
"query_cache_type" "OFF"
"query_cache_wlock_invalidate" "OFF"