MySQL Forums
Forum List  »  Optimizer & Parser

Can't get my indexes right
Posted by: Tobias Ritzau
Date: November 26, 2005 04:53PM

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 :|

Options: ReplyQuote


Subject
Views
Written By
Posted
Can't get my indexes right
3112
November 26, 2005 04:53PM
2087
November 27, 2005 07:28PM
1961
November 28, 2005 02:44AM
2152
November 28, 2005 03:17AM
1907
November 28, 2005 07:40AM
2020
November 28, 2005 02:19PM
2018
December 21, 2005 10:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.