MySQL select query with join and a lot of results optimization

I have two tables. The first one contains all the item informations and the second contains the item ID with a category ID. The reason of that is because an item can be in more than one category. I have about 500 000 items in my table.

我有两张桌子。第一个包含所有项目信息,第二个包含具有类别ID的项目ID。原因是因为一个项目可以在多个类别中。我的桌子里有大约50万件物品。

Here is a select query exemple:

这是一个选择查询例子:

SELECT SQL_CALC_FOUND_ROWS items.* 
FROM items 
    INNER JOIN cat
        ON items.iid=cat.iid 
WHERE (items.expire>'1308061323' AND cat.cid = '1') 
AND (items.code=71 OR items.code=23) 
ORDER BY price DESC 
LIMIT 0, 50

iid = item ID

iid =商品ID

cid = category ID

cid =类别ID

code = A code I use for search only.

code =我用于搜索的代码。

expire = The expiration time for an item

expire =项目的到期时间

I use SQL_CALC_FOUND_ROWS because I want to display the total matching results. I display only 50 items on the page (LIMIT 0, 50).

我使用SQL_CALC_FOUND_ROWS,因为我想显示总匹配结果。我在页面上只显示50个项目(LIMIT 0,50)。

When I execute that query, my php page take about 5 seconds to load (less than 1 without the query).

当我执行该查询时,我的php页面加载大约需要5秒钟(没有查询时少于1秒)。

Is there a way to optimize it?

有没有办法优化它?

Is it faster to use SQL_CALC_FOUND_ROWS or a second query with SELECT COUNT(*)?

使用SQL_CALC_FOUND_ROWS或使用SELECT COUNT(*)的第二个查询更快吗?

I heard about indexes, but I don't know how to use them and what they do. Can they help?

我听说过索引,但我不知道如何使用它们以及它们的用途。他们可以帮忙吗?

3 个解决方案

#1

Yes you can, by using index.

是的,您可以使用索引。

It is better to use 2 query, see here: Is there an effect on the speed of a query when using SQL_CALC_FOUND_ROWS in MySQL? and here: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?

最好使用2个查询,请参见此处:在MySQL中使用SQL_CALC_FOUND_ROWS时,是否会影响查询的速度?在这里:SQL_CALC_FOUND_ROWS或不是SQL_CALC_FOUND_ROWS?

Indexes help MySQL to find datas faster. Here, you need an index on code, cid, expire and price.

索引可帮助MySQL更快地查找数据。在这里,您需要有关代码,cid,过期和价格的索引。

You can create an index on items.code by executing this :

您可以通过执行以下命令在items.code上创建索引:

CREATE INDEX idx_items_code ON items (code);

It should improve your query immedialty.

它应该改善您的查询直接性。

My advice is to learn how index are working by reading some post on *.com.

我的建议是通过阅读*.com上的一些帖子来了解索引是如何工作的。

Here is a good one : What is an index in SQL Server?

这是一个很好的:SQL Server中的索引是什么?

Edit :

If indexes are that good, I can create indexes on every fields. What are the consequences of using an index?

如果索引很好,我可以在每个字段上创建索引。使用索引有什么后果?

Effectively, indexes ARE a silver bullet. It works every time. Too long query, boom, make an index. That's why, when you create a primary key, MySQL add an index to the field.

实际上,指数是一颗银弹。它每次都有效。太长的查询,繁荣,做一个索引。这就是为什么当你创建一个主键时,MySQL会为该字段添加一个索引。

On the other hand, index are taking space on the server, and the other operation : delete, update, insert, will take a little more time.

另一方面,索引在服务器上占用空间,而另一个操作:删除,更新,插入,将花费更多的时间。

So if you don't delete, update or insert too often, and select a lot, you can almost create an index for each field.

因此,如果您不经常删除,更新或插入,并选择很多,您几乎可以为每个字段创建索引。

The best is too fully understand the use of the index, so you could make good choice.

最好的是完全理解索引的使用,所以你可以做出很好的选择。

#2

Create an index on your cat table for the field iid

在cat表上为字段iid创建索引

Create an index on your items table for the fields code, iid, expire or iid, code, expire. See which is fastest.

在您的项目表上为字段代码,iid,expire或iid,代码,到期创建索引。看哪个是最快的。

You can use EXPLAIN in front of the select to get information on how to optimise the select. http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

您可以在选择前使用EXPLAIN来获取有关如何优化选择的信息。 http://dev.mysql.com/doc/refman/5.0/en/explain-output.html

#3

Your page will always load faster if you are not doing any selection on a database.

如果您没有对数据库进行任何选择,您的页面将始终加载更快。

I think your question is about concerns on the length of time your page is being served rather than the specific use of the function.

我认为您的问题是关于您的网页服务时间长度的问题,而不是功能的具体用途。

Have you tried setting a timestamp before and after the SQL, to determine the exact time it takes. You say you are limiting the result set to 50 rows, but how much data are your actually transferring to you page?

您是否尝试在SQL之前和之后设置时间戳,以确定所需的确切时间。您说您将结果集限制为50行,但实际传输到您页面的数据量是多少?

What other scripting is taking place? Are you processing the results and is that script optimised?

还有哪些脚本正在发生?您正在处理结果并且该脚本是否已优化?

Just thought I'd list a few other things you should be looking at too.

我以为我会列出一些你应该看的其他东西。

#1

Yes you can, by using index.