Simple left join query with abysmal performance.
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.