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:
我在mysql上非常环保,我需要一些清理查询的技巧。它通过一个站点用于多种变体。它有一些subquerys派生表和乐趣继续。继承人查询:
# 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)
3 个解决方案
#1
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
此页面可以合理地修复格式: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
#2
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.
有一件事我注意到这是一个很好的提示(可能是由更新的优化器过时),因为一般的性能改进是在需要总计时使用COUNT(1)。 COUNT(*)必须处理列,而COUNT(1)只需要考虑行。
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.
另一个提示是永远不要使用SELECT *。通常,您应该始终枚举您选择的列,以便在表更改时隔离代码。
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!
您真正想要做的一件事是将子查询移出SELECT块。联接通常比这些嵌套查询快得多。但请记住,优化器会为我们修复查询做大量的工作。如果优化器已经为我们做了,那么正确编写它可能不会显示出明显的改进!
Here's a link on subqueries in your select.
这是您的选择中子查询的链接。
#3
Just to show what i ment in the comment of Wouter van Nifterick´s anwswer. IMO, it should be:
只是为了展示Wouter van Nifterick的anwswer评论中的内容。 IMO,它应该是:
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'
#1
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
此页面可以合理地修复格式: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
#2
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.
有一件事我注意到这是一个很好的提示(可能是由更新的优化器过时),因为一般的性能改进是在需要总计时使用COUNT(1)。 COUNT(*)必须处理列,而COUNT(1)只需要考虑行。
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.
另一个提示是永远不要使用SELECT *。通常,您应该始终枚举您选择的列,以便在表更改时隔离代码。
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!
您真正想要做的一件事是将子查询移出SELECT块。联接通常比这些嵌套查询快得多。但请记住,优化器会为我们修复查询做大量的工作。如果优化器已经为我们做了,那么正确编写它可能不会显示出明显的改进!
Here's a link on subqueries in your select.
这是您的选择中子查询的链接。
#3
Just to show what i ment in the comment of Wouter van Nifterick´s anwswer. IMO, it should be:
只是为了展示Wouter van Nifterick的anwswer评论中的内容。 IMO,它应该是:
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'