Not making too much sense ...

MySQL & MariaDB Other MySQL® Questions
1

I have the following query:
EXPLAIN select p.id, p.sex, p.age from profiles as p force index(last_first_links_id) inner join profile_locations as pl force index (country_region_city_profile) on pl.profile_id = p.id where p.first_name = 28353 and p.last_name = 480607 and pl.country_id = 222 and pl.region_id = 3830 and pl.city_id = 1888105;

here is the explain on that:
±—±------------±------±-----±------------------------ ----±----------------------------±--------±-------------- -----------------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±------------------------ ----±----------------------------±--------±-------------- -----------------±-----±------------+
| 1 | SIMPLE | p | ref | last_first_links_id | last_first_links_id | 10 | const,const | 8750 | Using where |
| 1 | SIMPLE | pl | ref | country_region_city_profile | country_region_city_profile | 16 | const,const,const,p.id | 1 | Using index |
±—±------------±------±-----±------------------------ ----±----------------------------±--------±-------------- -----------------±-----±------------+

as you can see in Extra: Using where for table p !!! WHY WHY WHY

but I remove p.sex, p.age from select it will explain as this:

±—±------------±------±-----±------------------------ ----±----------------------------±--------±-------------- -----------------±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±------------------------ ----±----------------------------±--------±-------------- -----------------±-----±-------------------------+
| 1 | SIMPLE | p | ref | last_first_links_id | last_first_links_id | 10 | const,const | 8750 | Using where; Using index |
| 1 | SIMPLE | pl | ref | country_region_city_profile | country_region_city_profile | 16 | const,const,const,p.id | 1 | Using index |
±—±------------±------±-----±------------------------ ----±----------------------------±--------±-------------- -----------------±-----±-------------------------+

YOU MUST BE KIDDING ME??? I never had that kind of problems in MyISam…

here are the indexes on profiles table:
±---------±-----------±--------------------±------------ -±------------±----------±------------±---------±------ -±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±---------±-----------±--------------------±------------ -±------------±----------±------------±---------±------ -±-----±-----------±--------+
| profiles | 0 | PRIMARY | 1 | id | A | 134201142 | NULL | NULL | | BTREE | |
| profiles | 1 | profiles_FKIndex1 | 1 | actor_id | A | 17 | NULL | NULL | | BTREE | |
| profiles | 1 | active | 1 | active | A | 17 | NULL | NULL | YES | BTREE | |
| profiles | 1 | createdi | 1 | created | A | 67100571 | NULL | NULL | YES | BTREE | |
| profiles | 1 | thumbnail | 1 | thumbnail | A | 33550285 | NULL | NULL | YES | BTREE | |
| profiles | 1 | links | 1 | links | A | 17 | NULL | NULL | YES | BTREE | |
| profiles | 1 | age | 1 | age | A | 17 | NULL | NULL | YES | BTREE | |
| profiles | 1 | sex | 1 | sex | A | 17 | NULL | NULL | YES | BTREE | |
| profiles | 1 | bday | 1 | bday | A | 17 | NULL | NULL | YES | BTREE | |
| profiles | 1 | first_links_id | 1 | first_name | A | 17 | NULL | NULL | YES | BTREE | |
| profiles | 1 | first_links_id | 2 | links | A | 17 | NULL | NULL | YES | BTREE | |
| profiles | 1 | first_links_id | 3 | id | A | 134201142 | NULL | NULL | | BTREE | |
| profiles | 1 | last_first_links_id | 1 | last_name | A | 486236 | NULL | NULL | YES | BTREE | |
| profiles | 1 | last_first_links_id | 2 | first_name | A | 134201142 | NULL | NULL | YES | BTREE | |
| profiles | 1 | last_first_links_id | 3 | links | A | 134201142 | NULL | NULL | YES | BTREE | |
| profiles | 1 | last_first_links_id | 4 | id | A | 134201142 | NULL | NULL | | BTREE | |
| profiles | 1 | last_links_id | 1 | last_name | A | 1560478 | NULL | NULL | YES | BTREE | |
| profiles | 1 | last_links_id | 2 | links | A | 3947092 | NULL | NULL | YES | BTREE | |
| profiles | 1 | last_links_id | 3 | id | A | 134201142 | NULL | NULL | | BTREE | |
±---------±-----------±--------------------±------------ -±------------±----------±------------±---------±------ -±-----±-----------±--------+

2

the only solution i came up with so far is:

mysql> explain select * from profiles as pp join (select p.id from profiles as p inner join profile_locations as pl force index (country_region_city_profile) on pl.profile_id = p.id where p.name = 49643 and p.last_name = 342556 and pl.country_id = 222 and pl.region_id = 3825 and pl.city_id = 1873835) as ppp using(id);
±—±------------±-----------±-------±----------------- ----------------------------------------±------------------ ----------±--------±-------------±-----±---------------- ---------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-----------±-------±----------------- ----------------------------------------±------------------ ----------±--------±-------------±-----±---------------- ---------+
| 1 | PRIMARY | | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | pp | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | p | ref | PRIMARY,first_links_id,last_first_links_id,last_links_id | last_first_links_id | 10 | | 8364 | Using where; Using index |
| 2 | DERIVED | pl | ref | country_region_city_profile | country_region_city_profile | 16 | p.id | 1 | Using index |
±—±------------±-----------±-------±----------------- ----------------------------------------±------------------ ----------±--------±-------------±-----±---------------- ---------+
4 rows in set (0.04 sec)