Help with index in a view. Index not used. MySql Bug?
Hi there!
First of all I wanna thank you all becouse of participate in these useful forums, which help us a lot to improve our mysql databases.
I am trying to optimize my web app (social network), and i have a difficult issue because of a view that is not using the index properly.
I will show you the two tables ddl:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL auto_increment,
...
PRIMARY KEY (`id`),
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `friends` (
`id` int(11) NOT NULL auto_increment,
`idUser` int(11) NOT NULL COMMENT 'Usuario que forma parte de la relacion',
`idFriend` int(11) NOT NULL COMMENT 'Usuario que forma parte de la relacion',
...
PRIMARY KEY (`id`),
KEY `FK_friends_users_user` (`idUser`),
KEY `FK_friends_users_friend` (`idFriend`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `friends`
ADD CONSTRAINT `FK_friends_users_friend` FOREIGN KEY (`idFriend`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
ADD CONSTRAINT `FK_friends_users_user` FOREIGN KEY (`idUser`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;
As you can see, we have a common users table and a friendship table in witch relations between users are stored. In the friends table the fields `idUser` and `idFriend` do not have a specific order, I mean if A is friend of B, the row witch represents that could be Friends(id=x, idUser=A, idFriend=B) or Friends(id=x, idUser=B, idFriend=A) depending on witch one ask for the friendship.
In order to obtain the whole friends of the users in the system, I did create a view joining users with friends union it for obtain the friends you ask for them and those who ask for you:
CREATE OR REPLACE VIEW vfriends
SELECT f.idUser as idUser, u.id as idFriend, u.*
FROM users u INNER JOIN friends f ON u.id=f.idFriend
UNION
SELECT f.idFriend as idUser, u.id as idFriend, u.*
FROM users u INNER JOIN friends f ON u.id=f.idUser
In the web application we will filter by `vfriends`.`idUser`, but making:
EXPLAIN
select * from vfriends where idUser=x
I can see that the index is not working properly. The users tables are not beeing index with the primary key and type is ALL and have NULL ref.
If instead of making the select throgh the view and I use directly the select introducing the where filters inside the union selects, the indexes do work perfectly and the primary key index is used in the filter of the users table. The select would be this one:
SELECT f.idUser as idUser, u.id as idFriend, u.*
FROM users u INNER JOIN friends f ON u.id=f.idFriend
UNION
WHERE f.idUser=x
SELECT f.idFriend as idUser, u.id as idFriend, u.*
FROM users u INNER JOIN friends f ON u.id=f.idUser
WHERE f.idFriend=x
Knowing that both queries (using the view and not using it) are equivalent. My questions are:
¿Do MySql have a bug indexing correctly the views?
¿Do I need to specify in anyway with "force/use index" in the view the use of the primary key index? (I dont think so, because i have probe a lot with "force/use index" and did not work)
¿Should I forget the view and use always the expanded selects for the correct use of the index in Mysql? This would suposse to change the web application :-(
Thank you very much.