why is mysql ignoreing the index when using a view

Question

Why isn't mysql using the same index when using a view, as when running the query whitout view:

My query before i made a view:

DESCRIBE SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships
LEFT JOIN groups ON (group_id = target_id)
LEFT JOIN citizens ON (citizen_id = member_id)
WHERE target_type = 31 AND member_type = 11
AND member_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
|  1 | SIMPLE      | memberships | ref    | member,target | member     | 10      | const,const                         |    6 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |    1 |                          |
|  1 | SIMPLE      | citizens    | const  | citizen_id    | citizen_id | 8       | const                               |    1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+

It works, and is fast, its just a bit long to write each time i want to debug a membership. so i created a view based on that query:

CREATE VIEW group_members AS 
SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships 
LEFT JOIN groups ON (group_id = target_id) 
LEFT JOIN citizens ON (citizen_id = member_id) 
WHERE target_type = 31 AND member_type = 11;

but when i try to fetch the membership for 5613956 i get this:

DESCRIBE SELECT * FROM group_members WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
| id | select_type | table       | type   | possible_keys | key      | key_len | ref                                 | rows    | Extra                    |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
|  1 | SIMPLE      | memberships | ref    | member,target | target   | 1       | const                               |    5269 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id | 8       | interfaceways.memberships.target_id |       1 |                          |
|  1 | SIMPLE      | citizens    | ALL    | NULL          | NULL     | NULL    | NULL                                | 4766712 | Using where              |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+

Why is "possible_keys" for citizens NULL?

the field citizen_id is a SERIAL (= BIGINT(20) UNSIGNED UNIQUE NOT NULL AUTO_INCREMENT)

EDIT 1

if i use citizen_id insted of member_id in first query i get:

DESCRIBE SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date
FROM memberships
LEFT JOIN groups ON (group_id = target_id)
LEFT JOIN citizens ON (citizen_id = member_id)
WHERE target_type = 31 AND member_type = 11
AND citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
|  1 | SIMPLE      | citizens    | const  | citizen_id    | citizen_id | 8       | const                               |    1 |                          |
|  1 | SIMPLE      | memberships | ref    | member,target | member     | 10      | const,const                         |    6 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |    1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+

EDIT 2

A RIGHT JOIN moves the problem to the members table insted. (1054 x needed rows, compared with earlier 4 766 712 x needed rows)

CREATE VIEW group_members AS 
SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships 
RIGHT JOIN groups ON (group_id = target_id) 
RIGHT JOIN citizens ON (citizen_id = member_id) 
WHERE target_type = 31 AND member_type = 11;

DESCRIBE SELECT * FROM group_members WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+-------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows  | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+-------+--------------------------+
|  1 | SIMPLE      | citizens    | const  | citizen_id    | citizen_id | 8       | const                               |     1 |                          |
|  1 | SIMPLE      | memberships | index  | NULL          | member     | 45      | NULL                                | 10540 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |     1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+-------+--------------------------+

EDIT 3 A intresting note: this is faster, as it use the index, where the view itself fails

SELECT * FROM (SELECT * FROM group_members) AS t WHERE citizen_id = 5613956;
6 rows in set (0.03 sec)

DESCRIBE SELECT * FROM (SELECT * FROM group_members) AS t WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
| id | select_type | table       | type   | possible_keys | key        | key_len | ref                                 | rows | Extra                    |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+
|  1 | PRIMARY     | <derived2>  | ALL    | NULL          | NULL       | NULL    | NULL                                | 4423 | Using where              |
|  2 | DERIVED     | memberships | ref    | member,target | target     | 1       |                                     | 5270 | Using where; Using index |
|  2 | DERIVED     | groups      | eq_ref | group_id      | group_id   | 8       | interfaceways.memberships.target_id |    1 |                          |
|  2 | DERIVED     | citizens    | eq_ref | citizen_id    | citizen_id | 8       | interfaceways.memberships.member_id |    1 |                          |
+----+-------------+-------------+--------+---------------+------------+---------+-------------------------------------+------+--------------------------+

EDIT 4 YaKs comment

CREATE VIEW group_members AS 
SELECT membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death
FROM memberships 
LEFT JOIN groups ON (group_id = target_id) 
LEFT JOIN citizens ON (citizen_id = member_id) 
WHERE target_type = 31 AND member_type = 11;

DESCRIBE SELECT * FROM group_members WHERE citizen_id = 5613956;
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
| id | select_type | table       | type   | possible_keys | key      | key_len | ref                                 | rows    | Extra                    |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+
|  1 | SIMPLE      | memberships | ref    | member,target | target   | 1       | const                               |    5270 | Using where; Using index |
|  1 | SIMPLE      | groups      | eq_ref | group_id      | group_id | 8       | interfaceways.memberships.target_id |       1 |                          |
|  1 | SIMPLE      | citizens    | ALL    | NULL          | NULL     | NULL    | NULL                                | 4766736 | Using where              |
+----+-------------+-------------+--------+---------------+----------+---------+-------------------------------------+---------+--------------------------+

EDIT 5 jcho360 comment

ANALYZE TABLE group_members;
+-----------------------------+---------+----------+-------------------------------------------------+
| Table                       | Op      | Msg_type | Msg_text                                        |
+-----------------------------+---------+----------+-------------------------------------------------+
| interfaceways.group_members | analyze | Error    | 'interfaceways.group_members' is not BASE TABLE |
| interfaceways.group_members | analyze | error    | Corrupt                                         |
+-----------------------------+---------+----------+-------------------------------------------------+
2 rows in set (0.00 sec)

ANALYZE TABLE memberships;
+---------------------------+---------+----------+----------+
| Table                     | Op      | Msg_type | Msg_text |
+---------------------------+---------+----------+----------+
| interfaceways.memberships | analyze | status   | OK       |
+---------------------------+---------+----------+----------+
1 row in set (0.02 sec)

ANALYZE TABLE citizens;
+------------------------+---------+----------+----------+
| Table                  | Op      | Msg_type | Msg_text |
+------------------------+---------+----------+----------+
| interfaceways.citizens | analyze | status   | OK       |
+------------------------+---------+----------+----------+
1 row in set (0.11 sec)

ANALYZE TABLE groups;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| interfaceways.groups | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (0.01 sec)

Hm, let's first make these equal: what happens if you use `WHERE member_id = 5613956` rather then `WHERE citizen_id = 5613956`? Yes, they should yield the same data... but the query plan might differ. Otherwise, you could also use a `FORCE INDEX (member)` — Wrikken, Jul 05 '12 at 10:18
the view don't have the column member_id, but if i change the first query to citizen_id it still have possible_keys citizen_id, il add that plan above to — Puggan Se, Jul 05 '12 at 10:32
Ah, yes, hard to equalize then. Hm, using the `FORCE INDEX (member)` on both, how does that work? Also, you `LEFT ĴOIN` on `citizens`, but you are looking for a `citizen_id`.... Are you very sure you _need_ a `LEFT` join? — Wrikken, Jul 05 '12 at 11:00
I need the left join for: citizens.name, citizens.family, citizens.identification, and FORCE INDEX (member) did no difrence, except it says key=member, both the member and the target index have all used columnas as indicated by the 'Using index' — Puggan Se, Jul 05 '12 at 11:10
I'm not saying you don't need a `JOIN`, I'm saying why not a `RIGHT JOIN`? Do you allow members without a citizen record? Because that's a big part of the reason used indexes cannot be optimized. — Wrikken, Jul 05 '12 at 11:25
Could you please provide the execution plan on a view without the `COALESCE`'d `to_date` column? — RandomSeed, Jul 05 '12 at 12:04
@PugganSe can you use the `ANALIZE tablename` and try the quey again? — jcho360, Jul 05 '12 at 12:39
@jcho360 didn't understand why, but i added the output, the view faild as its not a table, and the other 3 just said ok, and the DESCRIBE SELECT * FROM group_members WHERE citizen_id = 5613956; havn't changed at all — Puggan Se, Jul 05 '12 at 12:46
@PugganSe ANALIZE is a Mysql command to fix index on a table, what is the engine of those tables? — jcho360, Jul 05 '12 at 12:48
@jcho360 they are all InnoDB, and your 'ANALIZE' is just a misspeling of 'ANALYZE TABLE tablename'? — Puggan Se, Jul 05 '12 at 13:25

jcho360 · Answer 1 · 2012-07-05T13:36:33.523

0

I was going to add Comment but it's too much.

try this:

Select * from 
(SELECT member_id, membership_id, group_id, groups.name AS group_name, citizen_id, citizens.name, citizens.family, citizens.identification, memberships.birth, memberships.death, COALESCE(memberships.death, '2099-12-31 23:59:59') AS to_date 
FROM memberships
LEFT JOIN groups ON (group_id = target_id)
LEFT JOIN citizens ON (citizen_id = member_id)
WHERE target_type = 31 AND member_type = 11) as table1
where member_id = 5613956;

and if you want to, you can make a describe of that query. I think that the same happen with a view table, so the where member_id=5613956will not use the citizens id index because it's not being used in the where condition to display that query.So the query is not exactly the same.

edited Jul 05 '12 at 13:36

answered Jul 05 '12 at 13:27

jcho360

3,724
1
15
24

1. "ERROR 1054 (42S22): Unknown column 'member_id' in 'where clause'", 2. if i replace member_id with citizen id in above query, i get the same result as in EDIT 3 – Puggan Se Jul 05 '12 at 13:35
the result got one extra column, otherwise its the same as EDIT 3 – Puggan Se Jul 05 '12 at 13:38
that's why all your index are different, because your not doing the same query – jcho360 Jul 05 '12 at 13:40
didn't catch that, how can 'SELECT * FROM group_members' take 0.03s AND 'SELECT * FROM group_members WHERE citizen_id = 5613956' take over 30s => how can a more speciific query get less optimezed? – Puggan Se Jul 05 '12 at 13:47
because you don't have an index, if you make a select * from group_member are they order by what??, I'm not sure if it will work, but try to add an order by citizen_ID to group_member when you create it – jcho360 Jul 05 '12 at 14:16
'ORDER BY citizen_id' only made the view slower, adding: "Using temporary; Using filesort" – Puggan Se Jul 05 '12 at 14:24
yeap, what I guess is that the query is not using an index to make that search that's why he's going thru row by row and that's why it's so slow, it's pretty much as to make an order by and find the ID – jcho360 Jul 05 '12 at 15:49
i know that it isn't using the index, the question at the end of my main post was 'Why is "possible_keys" for citizens NULL' – Puggan Se Jul 05 '12 at 16:05
because you don't have an index in the view, and you can't add it, when you make `where=5613956` you are making a match without any index because it's a view http://stackoverflow.com/questions/244226/is-it-possible-to-have-an-indexed-view-in-mysqlso the view it's like create a copy table without structure (if it's more than one table) – jcho360 Jul 05 '12 at 16:16
the link says: "a way to have an index over a view but have the table that is viewed remain unindexed", thats dosn't fit here, the tables are indexed, and it use the index when I don't have a where on it. the question is why it stops using that index if I add a where – Puggan Se Jul 05 '12 at 18:16

why is mysql ignoreing the index when using a view

1 Answers1