MySQL Forums
Forum List  »  Performance

Simple left join query with abysmal performance.
Posted by: Simon Courchesne
Date: June 15, 2005 12:24PM

I had to change servers last week and the new one seems to slow down incredibly when doing a certain query. I hope that I'm simply missing some index or some such, but after looking at the database and the code for the past 2 days, I am stump.

Here is a smaller version of what I am dealing with. 2 tables;

CREATE TABLE MOV_Movies (
MOV_ID mediumint(8) unsigned NOT NULL auto_increment,
MOV_Title varchar(120) NOT NULL default '',
MOV_YearOfProduction smallint(5) unsigned default NULL,
MOV_Length smallint(5) unsigned default NULL,
DIR_ID mediumint(8) unsigned NOT NULL default '0',
PRO_ID mediumint(8) unsigned NOT NULL default '0',
STU_ID mediumint(8) unsigned NOT NULL default '0',
SER_ID mediumint(8) unsigned NOT NULL default '0',
MOV_IsOnline char(1) NOT NULL default '',
MOV_Comment text NOT NULL,
MOV_Cover_F tinytext NOT NULL,
MOV_Cover_B tinytext NOT NULL,
MOV_BuyPrice double unsigned default NULL,
MOV_Movie_Type char(1) NOT NULL default '',
PRIMARY KEY (MOV_ID)
) TYPE=MyISAM;

CREATE TABLE LNK_ACT_MOV (
ACT_ID mediumint(8) unsigned NOT NULL default '0',
MOV_ID mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (ACT_ID,MOV_ID)
) TYPE=MyISAM;

The MOV_Movies table has a little less the 2000 entries and the LNK_ACT_MOV table has about 10 000 entries. Doing a simple query such as;

select * from MOV_Movies left join LNK_ACT_MOV on MOV_Movies.MOV_ID = LNK_ACT_MOV.MOV_ID;

Here is the description of the above query;

+-------------+-------+---------------+---------+---------+------+-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------------+-------+---------------+---------+---------+------+-------+-------------+
| MOV_Movies | ALL | NULL | NULL | NULL | NULL | 1844 | |
| LNK_ACT_MOV | index | NULL | PRIMARY | 6 | NULL | 10366 | Using index |
+-------------+-------+---------------+---------+---------+------+-------+-------------+

To execute this query, the server requires 1 minute. Which is unacceptable however you look at it. If anyone here could give me some help I would greatly appreciate it.

Options: ReplyQuote


Subject
Views
Written By
Posted
Simple left join query with abysmal performance.
6445
June 15, 2005 12:24PM


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.