How can I optimize this subqueried and Joined MySQL Query?

Question

I'm pretty green on mysql and I need some tips on cleaning up a query. It is used in several variations through out a site. Its got some subquerys derived tables and fun going on. Heres the query:

# Query_time: 2  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
SELECT *
  FROM (
  SELECT products . *, categories.category_name AS category, (
  SELECT COUNT( * )
  FROM distros
  WHERE distros.product_id = products.product_id) AS distro_count,
  (SELECT COUNT(*) FROM downloads WHERE downloads.product_id = products.product_id AND WEEK(downloads.date) = WEEK(curdate())) AS true_downloads,
  (SELECT COUNT(*) FROM views WHERE views.product_id = products.product_id AND WEEK(views.date) = WEEK(curdate())) AS true_views
  FROM products
  INNER JOIN categories ON products.category_id = categories.category_id ORDER BY created_date DESC, true_views DESC ) AS count_table
  WHERE count_table.distro_count > 0
  AND count_table.status = 'published'
  AND count_table.active = 1 LIMIT 0, 8

Heres the explain:

+----+--------------------+------------+-------+---------------+-------------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type        | table      | type  | possible_keys | key         | key_len | ref                                | rows | Extra                                        |
+----+--------------------+------------+-------+---------------+-------------+---------+------------------------------------+------+----------------------------------------------+
|  1 | PRIMARY            | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL                               |  232 | Using where                                  |
|  2 | DERIVED            | categories | index | PRIMARY       | idx_name    | 47      | NULL                               |   13 | Using index; Using temporary; Using filesort |
|  2 | DERIVED            | products   | ref   | category_id   | category_id | 4       | digizald_db.categories.category_id |    9 |                                              |
|  5 | DEPENDENT SUBQUERY | views      | ref   | product_id    | product_id  | 4       | digizald_db.products.product_id    |   46 | Using where                                  |
|  4 | DEPENDENT SUBQUERY | downloads  | ref   | product_id    | product_id  | 4       | digizald_db.products.product_id    |   14 | Using where                                  |
|  3 | DEPENDENT SUBQUERY | distros    | ref   | product_id    | product_id  | 4       | digizald_db.products.product_id    |    1 | Using index                                  |
+----+--------------------+------------+-------+---------------+-------------+---------+------------------------------------+------+----------------------------------------------+
6 rows in set (0.04 sec)

And the Tables:

mysql> describe products;
+---------------+--------------------------------------------------+------+-----+-------------------+----------------+
| Field         | Type                                             | Null | Key | Default           | Extra          |
+---------------+--------------------------------------------------+------+-----+-------------------+----------------+
| product_id    | int(10) unsigned                                 | NO   | PRI | NULL              | auto_increment |
| product_key   | char(32)                                         | NO   |     | NULL              |                |
| title         | varchar(150)                                     | NO   |     | NULL              |                |
| company       | varchar(150)                                     | NO   |     | NULL              |                |
| user_id       | int(10) unsigned                                 | NO   | MUL | NULL              |                |
| description   | text                                             | NO   |     | NULL              |                |
| video_code    | text                                             | NO   |     | NULL              |                |
| category_id   | int(10) unsigned                                 | NO   | MUL | NULL              |                |
| price         | decimal(10,2)                                    | NO   |     | NULL              |                |
| quantity      | int(10) unsigned                                 | NO   |     | NULL              |                |
| downloads     | int(10) unsigned                                 | NO   |     | NULL              |                |
| views         | int(10) unsigned                                 | NO   |     | NULL              |                |
| status        | enum('pending','published','rejected','removed') | NO   |     | NULL              |                |
| active        | tinyint(1)                                       | NO   |     | NULL              |                |
| deleted       | tinyint(1)                                       | NO   |     | NULL              |                |
| created_date  | datetime                                         | NO   |     | NULL              |                |
| modified_date | timestamp                                        | NO   |     | CURRENT_TIMESTAMP |                |
| scrape_source | varchar(215)                                     | YES  |     | NULL              |                |
+---------------+--------------------------------------------------+------+-----+-------------------+----------------+
18 rows in set (0.00 sec)

mysql> describe categories
    -> ;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| category_id      | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| category_name    | varchar(45)      | NO   | MUL | NULL    |                |
| parent_id        | int(10) unsigned | YES  | MUL | NULL    |                |
| category_type_id | int(10) unsigned | NO   |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe compatibilities
    -> ;
+------------------+------------------+------+-----+---------+----------------+
| Field            | Type             | Null | Key | Default | Extra          |
+------------------+------------------+------+-----+---------+----------------+
| compatibility_id | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name             | varchar(45)      | NO   |     | NULL    |                |
| code_name        | varchar(45)      | NO   |     | NULL    |                |
| description      | varchar(128)     | NO   |     | NULL    |                |
| position         | int(10) unsigned | NO   |     | NULL    |                |
+------------------+------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> describe distros
    -> ;
+------------------+--------------------------------------------------+------+-----+---------+----------------+
| Field            | Type                                             | Null | Key | Default | Extra          |
+------------------+--------------------------------------------------+------+-----+---------+----------------+
| id               | int(10) unsigned                                 | NO   | PRI | NULL    | auto_increment |
| product_id       | int(10) unsigned                                 | NO   | MUL | NULL    |                |
| compatibility_id | int(10) unsigned                                 | NO   | MUL | NULL    |                |
| user_id          | int(10) unsigned                                 | NO   |     | NULL    |                |
| status           | enum('pending','published','rejected','removed') | NO   |     | NULL    |                |
| distro_type      | enum('file','url')                               | NO   |     | NULL    |                |
| version          | varchar(150)                                     | NO   |     | NULL    |                |
| filename         | varchar(50)                                      | YES  |     | NULL    |                |
| url              | varchar(250)                                     | YES  |     | NULL    |                |
| virus            | enum('READY','PASS','FAIL')                      | YES  |     | NULL    |                |
| downloads        | int(10) unsigned                                 | NO   |     | 0       |                |
+------------------+--------------------------------------------------+------+-----+---------+----------------+
11 rows in set (0.01 sec)

mysql> describe downloads;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| product_id | int(10) unsigned | NO   | MUL | NULL    |                |
| distro_id  | int(10) unsigned | NO   | MUL | NULL    |                |
| user_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| ip_address | varchar(15)      | NO   |     | NULL    |                |
| date       | datetime         | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> describe views
    -> ;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| product_id | int(10) unsigned | NO   | MUL | NULL    |                |
| user_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| ip_address | varchar(15)      | NO   |     | NULL    |                |
| date       | datetime         | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Answer 1

One thing I noticed that is a nice tip (maybe outdated by newer optimizers) for general performance improvement is to use COUNT(1) when you want a total. COUNT(*) has to process columns whereas COUNT(1) just has to think about rows.

If this is no longer true, someone please drop a comment in here and I'll delete the post.

Another tip is to never use SELECT *. In general you should always enumerate the columns you are selecting against to insulate yourself against code breaking when tables change.

One thing you really are going to want to do is to move the subqueries out of the SELECT block. Joins are typically much faster than these nested queries. Remember though, Optimizers do an incredible amount of work trying to fix our queries for us. Writing it correctly may not show a marked improvement if the optimizer is already doing it for us!

Here's a link on subqueries in your select.

Answer 2

To start off, try to do something about the formatting and indenting of the query. Like this it's difficult to see exactly what's going on.

This page does a reasonable job to fix the formatting: http://www.dpriver.com/pp/sqlformat.htm

Anyway, it looks like the query basically tries to show the number of items in several tables.

Instead of doing it like this:

select
  (select count(*) from myothertable1 where myothertableid=myothertable.id),
  (select count(*) from myothertable2 where myothertableid=myothertable.id),
  (select count(*) from myothertable3 where myothertableid=myothertable.id)
from 
  myothertable

you should do something like this:

select
  count(myothertable1.id),
  count(myothertable2.id),
  count(myothertable3.id)
from 
  mytable,
  myothertable1,
  myothertable2,
  myothertable3
where 
  myothertableid1=mytable.id and
  myothertableid2=mytable.id and
  myothertableid3=mytable.id

Answer 3

I refrained from sub queries.

if you can do the sub query Separately in php , and put the result array that you get in the query, and run it , This reduces the query time significantly.

Answer 4

Just to show what i ment in the comment of Wouter van Nifterick´s anwswer. IMO, it should be:

select
  count(myothertable1.id),
  count(myothertable2.id),
  count(myothertable3.id)
from mytable
left outer join myothertable1 on (myothertableid1=mytable.id)
left outer join myothertable2 on (myothertableid2=mytable.id)
left outer join myothertable3 on (myothertableid3=mytable.id)
where mytable.field = 'value'