Re: Simple left join query with abysmal performance.
Simon Courchesne wrote:
> 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.
Since the join is made on MOV_ID and that key is not the first key in your composite index, the index is useless... Try instead: PRIMARY KEY (MOV_ID, ACT_ID).
Post the results!