sql optimize help needed

[Xuefer]

SELECT * FROM post WHERE forumid=1 ORDER BY ctime LIMIT x,10

ctime=creation time
both forumid and ctime is index separately
it take too much time when x is so large.
how can i optimize this query?

[lorax]

Do you have a primary key?

[Xuefer]

PRIMARY KEY postid
but.. why primary key?
it's not in "where" neither "order by" clause

how should i index?

[lorax]

Performance issues with MySQL are usually related to indexes - or lack thereof. There is no hard and fast rule for optimizing but in general, you want to put an index on the fields you will search. IF you don't then MySQL will use a less efficient search method - which will cost you. In your case you have the indexes in place.

Going by what you've given us I'd say that the ORDER BY portion is the likely culprit. Read more about how MySQL optimizes the ORDER BY [mysql.com] modifier.

In addition you say that when x is large - x being the offset for LIMIT - the response time is slow. While I'm not sure about this (I've never had to work with large numbers of records) it might be quicker to get the info into an array and organize that the way you want it. My thinking here is that you want to pare down the initial data set to the smallest size possible and get that data out of MySQL then use PHP to get it organized. The reason is that MySQL is working with data that lives on the disk (slow). PHP is working with data that lives in memory (fast). Learn more here [php-editors.com].

Re: primary key. It may be part of the reason the response is slow. This thread may be of use:

[webmasterworld.com...]

To get the skinny on Query performance you could use ANALYZE TABLE [mysql.com] and SHOW INDEX [mysql.com] to learn more about what MySQL sees.

This page from the MySQL manual [mysql.com] might give you some insight on how MySQL uses indexes.

hth

[Xuefer]

thanks
actually i'm using:
"select ... from post
left join ..
left join ..
left join ..
where post.forumid=$forumid
order by post.ctime
limit $offset,25"

is it true that: faster to
1. do:
"select postid from post
where post.forumid=$forumid
order by post.ctime
limit $offset,25"

(where clause is only on table "post")

2. $postids = implode(',', ...);
3. then do:
"select ... from post
left join ..
left join ..
left join ..
where post.postid IN ($postids)
order by FIND_IN_SET(post.postid, '$postids')"

[okrogius]

In addition to what has been posted above try to avoid "SELECT *". List what you want to select.

Even if you do in fact need to use all the fields in the table (which is unlikely), then you will still develop a good habit in case of, say you add another field to the table later on which does not need be to selected in your existing queries.