MySql SQL_CALC_FOUND_ROWS issue in FULL-TEXT search

Question

I am trying to add pagination to my results which query a FULL-TEXT indexed table. Here is the query:

Normal Query

SELECT *,MATCH(title) AGAINST ("+samsung +galaxy +s3" IN BOOLEAN MODE) 
as score FROM `deals` WHERE `image`!='' AND category=15032 ORDER BY score DESC;

It returns 183 rows.

Trying to do pagination

SELECT SQL_CALC_FOUND_ROWS *,MATCH(title) AGAINST 
("+samsung +galaxy +s3" IN BOOLEAN MODE) as score FROM `deals` 
WHERE `image`!='' AND category=15032 ORDER BY score DESC LIMIT 8;

Return 8 rows as intended as i want to show 8 items per page.

and then

SELECT FOUND_ROWS();

It returns 20 items as opposed to 183 as was coming in the original query. I am not sure as to what may be causing this. Can you please help me with this.

Thanks.

jcho360 · Accepted Answer · 2012-06-21T12:37:48.023

You are doing it wrong, to do the pagination you need two limits, the begin and the end, look this example and add the limit correctly to your code

mysql> select * from t1 order by actor_id;
+----------+-------------+--------------+---------------------+
| actor_id | first_name  | last_name    | last_update         |
+----------+-------------+--------------+---------------------+
|        1 | PENELOPE    | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK        | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED          | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER    | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY      | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE       | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE       | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW     | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE         | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN   | GABLE        | 2006-02-15 04:34:33 |
|       11 | ZERO        | CAGE         | 2006-02-15 04:34:33 |
|       12 | KARL        | BERRY        | 2006-02-15 04:34:33 |
|       13 | UMA         | WOOD         | 2006-02-15 04:34:33 |
|       14 | VIVIEN      | BERGEN       | 2006-02-15 04:34:33 |
|       15 | CUBA        | OLIVIER      | 2006-02-15 04:34:33 |
|       16 | FRED        | COSTNER      | 2006-02-15 04:34:33 |
|       17 | HELEN       | VOIGHT       | 2006-02-15 04:34:33 |
|       18 | DAN         | TORN         | 2006-02-15 04:34:33 |
|       19 | BOB         | FAWCETT      | 2006-02-15 04:34:33 |
|       20 | LUCILLE     | TRACY        | 2006-02-15 04:34:33 |
....

+----------+-------------+--------------+---------------------+
201 rows in set (0.00 sec)

now you apply the limits, begin and end

mysql> select * from t1 order by actor_id limit 0,5;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from t1 order by actor_id limit 6,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        7 | GRACE      | MOSTEL    | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK     | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE     | 2006-02-15 04:34:33 |
|       11 | ZERO       | CAGE      | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from t1 order by actor_id limit 11,5;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|       12 | KARL       | BERRY     | 2006-02-15 04:34:33 |
|       13 | UMA        | WOOD      | 2006-02-15 04:34:33 |
|       14 | VIVIEN     | BERGEN    | 2006-02-15 04:34:33 |
|       15 | CUBA       | OLIVIER   | 2006-02-15 04:34:33 |
|       16 | FRED       | COSTNER   | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
5 rows in set (0.00 sec)

so the Idea is you use LIMIT $begin,$number_results, and when you click next it will take the number_result + total_result_per_page and put it in the $begin. Lets say that you are in page 3, and you show 10 results per page.($begin=10+10+10=30)

select ..... where .... order ... LIMIT 30,10

EDI:

the found_rows will return from the beginning of the table to your limit,

 mysql> select * from t2 order by actor_id limit 10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        1 | PENELOPE   | GUINESS      | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG     | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

mysql> select * from t2 order by actor_id limit 2,10;
+----------+------------+--------------+---------------------+
| actor_id | first_name | last_name    | last_update         |
+----------+------------+--------------+---------------------+
|        3 | ED         | CHASE        | 2006-02-15 04:34:33 |
|        4 | JENNIFER   | DAVIS        | 2006-02-15 04:34:33 |
|        5 | JOHNNY     | LOLLOBRIGIDA | 2006-02-15 04:34:33 |
|        6 | BETTE      | NICHOLSON    | 2006-02-15 04:34:33 |
|        7 | GRACE      | MOSTEL       | 2006-02-15 04:34:33 |
|        8 | MATTHEW    | JOHANSSON    | 2006-02-15 04:34:33 |
|        9 | JOE        | SWANK        | 2006-02-15 04:34:33 |
|       10 | CHRISTIAN  | GABLE        | 2006-02-15 04:34:33 |
|       11 | ZERO       | CAGE         | 2006-02-15 04:34:33 |
|       12 | KARL       | BERRY        | 2006-02-15 04:34:33 |
+----------+------------+--------------+---------------------+
10 rows in set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|           12 |
+--------------+
1 row in set (0.00 sec)

It's 12 because 10 result displayed + the id 1 and 2 + 10 displayed results

score 0 · Answer 2 · edited Aug 25 '14 at 11:53

0

Try to use HAVING instead of WHERE

SELECT SQL_CALC_FOUND_ROWS 
    *, 
    MATCH(title) AGAINST ("+samsung +galaxy +s3" IN BOOLEAN MODE) as score 
FROM `deals` 
HAVING`image`!='' 
    AND category=15032 
ORDER BY score DESC 
LIMIT 8;

edited Aug 25 '14 at 11:53

msrd0

7,816
9
47
82

answered Aug 25 '14 at 11:45

user3314859

1

MySql SQL_CALC_FOUND_ROWS issue in FULL-TEXT search

2 Answers2