CODEWITHSUNDEEP
phpmysqloptimizationquery-optimization
Marm
Marm

Reputation: 873

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.

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

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).

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

Upvotes: 2

Views: 640

Answers (3)

Cyril Gandon
Cyril Gandon

Reputation: 17058

  1. Yes you can, by using index.
  2. 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?
  3. Indexes help MySQL to find datas faster. Here, you need an index on code, cid, expire and price.

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

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 stackoverflow.com.

Here is a good one : What is an index in 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.

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.

Upvotes: 3

T9b
T9b

Reputation: 3502

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?

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.

Upvotes: 0

Jaydee
Jaydee

Reputation: 4158

Create an index on your cat table for the field iid

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

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

Upvotes: 1

Related Questions