Hi I know there is a lot of topics dedicated to query optimizing strategies, but this one is so specific I couldnt find the answer anywhere on the interenet.
嗨，我知道有很多主题都是关于查询优化策略的，但是这个主题太具体了，我在网络上找不到答案。
I have large table of product in eshop (appx. 180k rows) and the table has 65 columns. Yeah yeah I know its quite a lot, but I store there information about books, dvds, bluerays and games.
我在eshop (appx)有一个大的产品表。表有65列。是的，是的，我知道很多，但是我把书，dvd，蓝光和游戏的信息都储存在那里。
Still I am not considering a lot of cols into query, but the select is still quite tricky. There are many conditions that need to be considered and compared. Query below
尽管如此，我并没有考虑大量的cols查询，但是选择仍然相当棘手。有许多条件需要考虑和比较。下面的查询
SELECT *
FROM products
WHERE production = 1
AND publish_on < '2012-10-23 11:10:06'
AND publish_off > '2012-10-23 11:10:06'
AND price_vat > '0.5'
AND ean <> ''
AND publisher LIKE '%Johnny Cash%'
ORDER BY bought DESC, datec DESC, quantity_storage1 DESC, quantity_storege2 DESC, quantity_storage3 DESC
LIMIT 0, 20
I have already tried to put there indexes one by one on cols in where clause and even in order by clause, then I tried to create compound index on (production, publish_on, publish_off, price_vat, ean).
我已经尝试在where子句中，甚至在order by子句中，一个一个地在cols上建立索引，然后尝试在(production, publish_on, publish_off, price_vat, ean)上创建复合索引。
Query is still slow (couple of seconds) and it need to be fast since its eshop solution and people are leaving as they are not getting their results fast. And I am still not counting the time I need to perform the search for all found rows so I can make paging.
查询仍然很慢(几秒钟)，而且由于eshop的解决方案，由于人们不能快速得到结果，所以他们正在离开。而且我还没有计算需要执行搜索的时间，所以我可以进行分页。
I mean, the best way to make it quick is to simplify the query, but all the conditions and sorting is a must in this case.
我的意思是，使它快速的最好方法是简化查询，但是在这种情况下，所有的条件和排序都是必须的。
Can anyone help with this kind of issue? Is it even possible to speed this kind of query up, or is there any other way how I can for example simplify the query and leave the rest on php engine to sort the results..
有人能帮忙解决这类问题吗?是否有可能加快这种查询的速度，或者有没有其他方法可以简化查询，并让rest在php引擎中对结果进行排序。
Oh, Iam really clueless in this.. Share your wisdom peple, please...
哦，我真搞不懂这事。请分享你的智慧。
Many thanks in advance
提前感谢
2 个解决方案
#1
4
First of all be sure what you want to select and erase the '*'
首先，要确定要选择什么并删除" * "
Select * from
with something more specific
更具体的东西
Select id, name, ....
There is no Join or anything other in your table so the speed up options are quite small I think.
您的表中没有连接或其他内容，所以我认为加速选项非常小。
-
Check that your mysql Server can use enough memory. Have a look at this confis in your my.cnf
检查您的mysql服务器是否可以使用足够的内存。看看这个糖果在你的。cnf
- key_buffer_size = 384M;
- key_buffer_size = 384;
- myisam_sort_buffer_size = 64M;
- myisam_sort_buffer_size = 64;
- thread_cache_size = 8;
- thread_cache_size = 8;
- query_cache_size = 64M
- query_cache_size = 64
-
Have a look a max allowed concurrency. mysql recommends CPU's*2
看一下最大允许并发性。mysql建议CPU的* 2
thread_concurrency = 4
thread_concurrency = 4
-
You should really thinks about splitting the table depending on informations you use and on standard normalization. If possible.
您应该考虑根据使用的信息和标准规范化来拆分表。如果可能的话。
-
If it's a productive system with no way to split the tables then think about a caching server. But this will only help if you have a lot of recurring querys that are the same.
如果它是一个生产系统，没有办法分割表，那么考虑一下缓存服务器。但这只会帮助如果你有很多重复的查询是相同的。
This is what I would do when knowing nothing about the underlying implementation or the system at all.
当我对底层实现或系统一无所知时，我就会这样做。
Edit: Making as many columns indexable as you can won't necessarily speed up your system. The more indexes ≠ the more speed.
编辑:使尽可能多的列可索引，并不一定会加速您的系统。索引≠越多速度。
#2
0
thx to all of you for good remarks..
谢谢大家的夸奖。
I found the solution probably 'cause I was able to reduce query time from 2,8s down to 0,3 sec.
我找到了解决方案，因为我可以将查询时间从28s减少到0,3秒。
SOLUTION: using SELECT * is really naive on large tables (65cols) so I realized I only need 25 of them on page - other can be easily used on product page itself.
解决方案:在大型表(65cols)上使用SELECT *实在太幼稚了，因此我意识到我只需要在页面上使用25个表——其他表可以在产品页面上轻松使用。
I also reindexed my table little bit. I created compound index on
我也用驯鹿装饰我的桌子。我创建了复合索引
production, publish_on, publish_off, price_vat, ean
生产，发布，发布，发布，发布
then I created another one specificaly for search including cols
然后我创建了另一个特别的搜索，包括cols
title, publisher, author
题目、出版社、作者
last thing what I did was to use query like
我做的最后一件事是使用查询like
SELECT SQL_CALC_FOUND_ROWS ID, title, alias, url, type, preorder, subdescription,....
which allowed me to calculate influenced rows quicker using
哪一种方法可以让我更快地计算受影响的行
mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0)
after mysql_query()... However I cannot understand how it could be quicker, because EXPLAIN EXTENDED says the query is not using any index, its still 0,5s quicker then calculate the number of rows in individual query.
后mysql_query()……但是我不能理解它是如何更快的，因为解释扩展说查询没有使用任何索引，它仍然是0,5s更快，然后计算单个查询中的行数。
It seems to be working rather fine. If the order by clause wasnt there it would be evil quick, but thats something I have no influence on.
看起来还不错。如果“逐句命令”不存在的话，很快就会变成“恶”，但这是我没有影响力的。
Still need to check my server settings...
仍然需要检查我的服务器设置…
Thank y'all for all your help..
谢谢你的帮助。
#1
4
First of all be sure what you want to select and erase the '*'
首先，要确定要选择什么并删除" * "
Select * from
with something more specific
更具体的东西
Select id, name, ....
There is no Join or anything other in your table so the speed up options are quite small I think.
您的表中没有连接或其他内容，所以我认为加速选项非常小。
-
Check that your mysql Server can use enough memory. Have a look at this confis in your my.cnf
检查您的mysql服务器是否可以使用足够的内存。看看这个糖果在你的。cnf
- key_buffer_size = 384M;
- key_buffer_size = 384;
- myisam_sort_buffer_size = 64M;
- myisam_sort_buffer_size = 64;
- thread_cache_size = 8;
- thread_cache_size = 8;
- query_cache_size = 64M
- query_cache_size = 64
-
Have a look a max allowed concurrency. mysql recommends CPU's*2
看一下最大允许并发性。mysql建议CPU的* 2
thread_concurrency = 4
thread_concurrency = 4
-
You should really thinks about splitting the table depending on informations you use and on standard normalization. If possible.
您应该考虑根据使用的信息和标准规范化来拆分表。如果可能的话。
-
If it's a productive system with no way to split the tables then think about a caching server. But this will only help if you have a lot of recurring querys that are the same.
如果它是一个生产系统，没有办法分割表，那么考虑一下缓存服务器。但这只会帮助如果你有很多重复的查询是相同的。
This is what I would do when knowing nothing about the underlying implementation or the system at all.
当我对底层实现或系统一无所知时，我就会这样做。
Edit: Making as many columns indexable as you can won't necessarily speed up your system. The more indexes ≠ the more speed.
编辑:使尽可能多的列可索引，并不一定会加速您的系统。索引≠越多速度。
#2
0
thx to all of you for good remarks..
谢谢大家的夸奖。
I found the solution probably 'cause I was able to reduce query time from 2,8s down to 0,3 sec.
我找到了解决方案，因为我可以将查询时间从28s减少到0,3秒。
SOLUTION: using SELECT * is really naive on large tables (65cols) so I realized I only need 25 of them on page - other can be easily used on product page itself.
解决方案:在大型表(65cols)上使用SELECT *实在太幼稚了，因此我意识到我只需要在页面上使用25个表——其他表可以在产品页面上轻松使用。
I also reindexed my table little bit. I created compound index on
我也用驯鹿装饰我的桌子。我创建了复合索引
production, publish_on, publish_off, price_vat, ean
生产，发布，发布，发布，发布
then I created another one specificaly for search including cols
然后我创建了另一个特别的搜索，包括cols
title, publisher, author
题目、出版社、作者
last thing what I did was to use query like
我做的最后一件事是使用查询like
SELECT SQL_CALC_FOUND_ROWS ID, title, alias, url, type, preorder, subdescription,....
which allowed me to calculate influenced rows quicker using
哪一种方法可以让我更快地计算受影响的行
mysql_result(mysql_query("SELECT FOUND_ROWS()"), 0)
after mysql_query()... However I cannot understand how it could be quicker, because EXPLAIN EXTENDED says the query is not using any index, its still 0,5s quicker then calculate the number of rows in individual query.
后mysql_query()……但是我不能理解它是如何更快的，因为解释扩展说查询没有使用任何索引，它仍然是0,5s更快，然后计算单个查询中的行数。
It seems to be working rather fine. If the order by clause wasnt there it would be evil quick, but thats something I have no influence on.
看起来还不错。如果“逐句命令”不存在的话，很快就会变成“恶”，但这是我没有影响力的。
Still need to check my server settings...
仍然需要检查我的服务器设置…
Thank y'all for all your help..
谢谢你的帮助。