# Not making too much sense ...

**URL:** <https://forums.percona.com/t/not-making-too-much-sense/871>\
**Category:** Other MySQL® Questions\
**Created:** [August 6, 2008, 10:36am UTC](https://forums.percona.com/t/not-making-too-much-sense/871 "2008-08-06T10:36:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![rookie7799](https://avatars.discourse-cdn.com/v4/letter/r/13edae/32.png) [@rookie7799](https://forums.percona.com/u/rookie7799)\
**Post date:** [August 6, 2008, 10:36am UTC](https://forums.percona.com/t/not-making-too-much-sense/871/1 "2008-08-06T10:36:57Z")

</div>

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 | |  
±---------±-----------±--------------------±------------ -±------------±----------±------------±---------±------ -±-----±-----------±--------+

---

<div class="post-metadata">

**Author:** ![rookie7799](https://avatars.discourse-cdn.com/v4/letter/r/13edae/32.png) [@rookie7799](https://forums.percona.com/u/rookie7799)\
**Post date:** [August 6, 2008, 1:33pm UTC](https://forums.percona.com/t/not-making-too-much-sense/871/2 "2008-08-06T13:33:30Z")

</div>

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)