select takes 20 seconds, delete still running @ 30minutes

Question

This select query takes about 20 seconds to complete.

 select Count(*)
   from products as bad_rows
   inner join (
   select pid, MAX(last_updated_date) as maxdate
      from products
       group by pid
         having count(*) > 1
      ) as good_rows on good_rows.pid= bad_rows.pid
        and good_rows.maxdate <> bad_rows.last_updated_date
        where bad_rows.available = 0

The delete on the other hand is still running after 30 minutes !

  delete bad_rows
     from products as bad_rows
      inner join (
       select pid, MAX(last_updated_date) as maxdate
          from products
           group by pid
             having count(*) > 1
          ) as good_rows on good_rows.pid= bad_rows.pid
            and good_rows.maxdate <> bad_rows.last_updated_date
            where bad_rows.available = 0

Why ?

Table Schema is as follows:

enter image description here

Explain for the select is as follows:

+----+-------------+------------+------+---------------+------+---------+------+-------+--------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra                          |
+----+-------------+------------+------+---------------+------+---------+------+-------+--------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |  6253 |                                |
|  1 | PRIMARY     | bad_rows   | ALL  | NULL          | NULL | NULL    | NULL | 34603 | Using where; Using join buffer |
|  2 | DERIVED     | products   | ALL  | NULL          | NULL | NULL    | NULL | 34603 | Using temporary; Using filesort|
+----+-------------+------------+------+---------------+------+---------+------+-------+--------------------------------

What does the `EXPLAIN` plan look like for your query? How many rows? — doublesharp, May 14 '14 at 19:25
How many indexes or function based on the table? Deletes take longer when indexes exist due to the overhead of managing the indexes. or features like function based indexes... http://stackoverflow.com/questions/10595037/is-it-possible-to-have-function-based-index-in-mysql — xQbert, May 14 '14 at 19:25

score 1 · Answer 1 · answered May 14 '14 at 19:43

1

ok so I just googled the results explain which hinted that my query could be slow because of not having indexes on pid. It didn't actually say that, but I just had a hunch from reading about the results of Explain. SO I added a index on pid and voila. Delete over in 1 minute!!

answered May 14 '14 at 19:43

gyaani_guy

3,191
8
43
51

1

http://use-the-index-luke.com is an excellent introduction to how to use indexes. – Andy Lester May 14 '14 at 19:47

select takes 20 seconds, delete still running @ 30minutes

1 Answers1