2

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
  • iid = item ID
  • cid = category ID
  • code = A code I use for search only.
  • expire = The expiration time for an item

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

  • Is there a way to optimize it?
  • Is it faster to use SQL_CALC_FOUND_ROWS or a second query with SELECT COUNT(*)?
  • I heard about indexes, but I don't know how to use them and what they do. Can they help?
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
Marm
  • 863
  • 2
  • 15
  • 30

3 Answers3

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

Community
  • 1
  • 1
Cyril Gandon
  • 16,830
  • 14
  • 78
  • 122
  • If indexes are that good, I can create indexes on every fields. What are the consequences of using an index? – Marm Jun 14 '11 at 15:00
  • One or two well designed indexes will, in most cases, be more efficient that a scatter gun approach. – Jaydee Jun 14 '11 at 15:03
1

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

Jaydee
  • 4,138
  • 1
  • 19
  • 20
0

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.

T9b
  • 3,312
  • 5
  • 31
  • 50