Can't get my indexes right
I'm sorry but I simply do not know what to search for to find the answer to my question so I hope someone can help me. I guess I do not understand how MySQL uses indexes. I have a local copy of IMDB but unfortunately they do not provide their keys so I have to do a translation to local keys to be able to add own stuff. To link between my DB and IMDB I have a translation table with the IMDB key and the name of the movie/person. I call my key mid and the IMDB key imid.
The translation is defined as:
CREATE TABLE `mids` (
`name` tinytext NOT NULL,
`imid` tinytext NOT NULL,
PRIMARY KEY (`imid`(32)),
UNIQUE KEY `name` (`name`(128))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The following tables holds IMDB info:
CREATE TABLE `movies` (
`mid` int(11) NOT NULL default '0',
`name` tinytext NOT NULL,
PRIMARY KEY (`mid`),
UNIQUE KEY `name` (`name`(128)),
KEY `mid` (`mid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE `ratings` (
`mid` int(11) NOT NULL default '0',
`average` double default NULL,
PRIMARY KEY (`mid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
And finally this table holds the ratings of me and my friends:
CREATE TABLE `user_rating` (
`uid` int(11) default NULL,
`imid` varchar(16) default NULL,
`grade` int(11) default NULL,
UNIQUE KEY `user_rating` (`uid`,`imid`),
KEY `imid` (`imid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
I use the following query to list the ratings:
SELECT mids.name, movies.mid, mids.imid, avg(grade) as g, count(grade) as v, average
FROM test.mids
JOIN test.movies ON (mids.name = movies.name)
JOIN test.ratings ON (ratings.mid = movies.mid)
JOIN test.user_rating ON (user_rating.imid = mids.imid)
GROUP BY mids.imid
ORDER BY g DESC, mids.name;
but it is very slow. Here is the explain:
+----+-------------+-------------+--------+------------------+---------+---------+------------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+--------+------------------+---------+---------+------------------+--------+---------------------------------+
| 1 | SIMPLE | mids | ALL | PRIMARY,name | NULL | NULL | NULL | 353 | Using temporary; Using filesort |
| 1 | SIMPLE | user_rating | ref | imid | imid | 17 | test.mids.imid | 2 | Using where |
| 1 | SIMPLE | ratings | ALL | PRIMARY | NULL | NULL | NULL | 115024 | |
| 1 | SIMPLE | movies | eq_ref | PRIMARY,name,mid | PRIMARY | 4 | test.ratings.mid | 1 | Using where |
+----+-------------+-------------+--------+------------------+---------+---------+------------------+--------+---------------------------------+
My question is why it does not use the mid index to find the ratings.average? I can't even force it to use the index so I guess that I'm doing something terribly wrong :|