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)
