index pushdown - Bug or Side Effect
Two days ago I upgraded my db server from mysql to Maria 5.5.23, the load shot up from .5 to 20+ and the site slowly ground to a halt. I tracked it down to the query below which previously ran in < .001 secs now taking 14 - 15 secs. I guessed that the index was not being used and a full table scan must be the cause. Adding SET optimizer_switch="index_condition_pushdown=off" cured the problem.
So my question is, is this a bug or just something to be cautious of ?
SELECT SQL_NO_CACHE human_id, first_name, last_name, nationality, dob, residence_id, stance, r, RD, residence.towncity_name AS residence_towncity_name, residence.country_code AS residence_country_code, residence.region_name AS residence_region_name FROM humans LEFT JOIN towncities AS residence ON residence.towncity_id = residence_id WHERE division = "Minimumweight" AND is_boxer = "3" AND sex = "m" ORDER BY r DESC LIMIT 275 , 25
Status Time starting 0.000064 checking permissions 0.000011 checking permissions 0.000011 Opening tables 0.000020 System lock 0.000011 Table lock 0.000012 init 0.000037 optimizing 0.000023 statistics 0.000157 preparing 0.000035 executing 0.000011 Sorting result 0.000011 Sending data 14.437780 end 0.000027 query end 0.000014 closing tables 0.000018 freeing items 0.000034 logging slow query 0.000016 logging slow query 0.000057 cleaning up 0.000019
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE humans ref is_boxer,division_boxer_sex_r division_boxer_sex_r 3 const,const,const 1258 Using index condition; Using where 1 SIMPLE residence eq_ref PRIMARY PRIMARY 2 v3.humans.residence_id 1 Using where
Answer Answered by elenst in this comment.
Thank you for the data, it helped to reproduce the problem. I filed a bug on your behalf on launchpad: https://bugs.launchpad.net/maria/+bug/1000051 You can track further progress there.