Re: Alternative to FOUND_ROWS()
Posted by: jun kazawa
Date: February 14, 2009 01:15PM

If it can be of help for anybody, this is what we finally did
- our table is MyISAM (has a FT index) will millions of rows
- primary is PID
- we limited the result to 400 rows
- a page contains 20 rows

A subquery returns the max 400+1 PIDs, from which we take the 20 PIDs using SQL_CALC_FOUND_ROWS / FOUND_ROWS.
Indexing / order by and limit optimization performs well in the subquery and the result comes much faster than before (where it had to count all the generic terms in the table).

[query has been simplified]

SELECT SQL_CALC_FOUND_ROWS t.PID
FROM
(SELECT PID FROM tbl WHERE MATCH(BigText) AGAINST('+genericterm' IN BOOLEAN MODE)
ORDER BY PID DESC LIMIT 401) as t
ORDER BY t.PID DESC LIMIT Page*20,20;

Note: we count 401 rows (and not 400) to assess that the query went over the limit (and the user is informed).

Options: ReplyQuote


Subject
Written By
Posted
February 08, 2009 01:07PM
February 10, 2009 12:08AM
February 11, 2009 10:21AM
Re: Alternative to FOUND_ROWS()
February 14, 2009 01:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.