Announcement

Announcement Module
Collapse
No announcement yet.

Not making too much sense ...

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Not making too much sense ...

    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_li nks_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)

    Comment

    Working...
    X