I have a query that joins many tables and I've tried many different ways to optimize it so that it runs at an acceptable speed (less than 5 seconds). Currently it takes 3mins +
The first thing I don't understand is why EXPLAIN shows 143090 possible rows, when on just one fo the WHERE parts I have only 73441 rows that match (this is for the s.photo_status_id=1).
Any help is much appreciated, thanks!
mysql> explain SELECT un.uid, un.username_name, sd.subprofile_details_headline, p.profile_birthday, pg.profile_gender_name, cr.country_region_name, c.country_name, s.photo_status_id FROM (iwantu_country_region cr INNER JOIN (iwantu_country c INNER JOIN (iwantu_username un INNER JOIN (((iwantu_profile_gender pg INNER JOIN iwantu_profile p ON pg.profile_gender_id = p.profile_gender_id) INNER JOIN iwantu_subprofile_1 s ON p.uid = s.uid) INNER JOIN iwantu_subprofile_details_1 sd ON p.uid = sd.uid) ON un.uid = p.uid) ON c.country_id = p.country_id) ON cr.country_region_id = p.country_region_id) WHERE s.photo_status_id=1 AND cr.country_region_id='3890' AND p.profile_gender_id IN (2) AND (p.profile_birthday BETWEEN -158094000 AND 315205200) ORDER BY s.subprofile_last_update DESC LIMIT 0, 25 ;
+----+-------------+-------+--------+-----------------------------------------------------------------------------------------+------------------------+---------+----------------------------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------------+------------------------+---------+----------------------------+--------+----------------+
| 1 | SIMPLE | cr | const | PRIMARY | PRIMARY | 3 | const | 1 | Using filesort |
| 1 | SIMPLE | s | ref | PRIMARY,idx_uid,idx_subprofile_picture | idx_subprofile_picture | 1 | const | 143090 | Using where |
| 1 | SIMPLE | p | eq_ref | PRIMARY,idx_profile_birthday,idx_profile_gender_id,idx_country_id,idx_country_region_id | PRIMARY | 3 | iwantu.s.uid | 1 | Using where |
| 1 | SIMPLE | pg | eq_ref | PRIMARY | PRIMARY | 1 | iwantu.p.profile_gender_id | 1 | |
| 1 | SIMPLE | un | eq_ref | PRIMARY | PRIMARY | 3 | iwantu.p.uid | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 1 | iwantu.p.country_id | 1 | |
| 1 | SIMPLE | sd | eq_ref | PRIMARY,idx_uid | PRIMARY | 3 | iwantu.p.uid | 1 | |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------------+------------------------+---------+----------------------------+--------+----------------+
