List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:August 28 2009 4:54pm
Subject:Re: Very Slow Query
View as plain text  
In the last episode (Aug 28), Jia Chen said:
> Thanks for reply!
> 
> Yes, it is very slow too  if I just execute the "select ..." part.
> 
> When I run
> mysql> explain extended select a.*, b.assname, b.cname, b.cusip, b.own, 
> b.pname, b.sname from nber1999.pat1 as a inner join nber1999.compusta1 
> asb  on a.assignee=b.assignee;
> I got
>
> +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
> | id | select_type | table | type | possible_keys | key  | key_len |  ref  | rows   
> | Extra       |
>
> +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
> |  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    |  NULL |    4906
> |             |
> |  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    |  NULL | 2089903
> | Using where |
>
> +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
> 2 rows in set, 1 warning (0.00 sec)

Ouch.  Add an index on pat1.assignee.  Mysql currently has to scan your
entire pat1 table for every row in compusta1 to find matching rows.  In
general, you want an index on any fields used in a WHERE clause.

-- 
	Dan Nelson
	dnelson@stripped
Thread
Very Slow QueryJia Chen28 Aug
  • Re: Very Slow QueryDan Nelson28 Aug
    • Re: Very Slow QueryJia Chen28 Aug
      • Re: Very Slow QueryDan Nelson28 Aug
        • Re: Very Slow QueryJia Chen28 Aug
      • RE: Very Slow QueryJohn28 Aug