在全文搜索中会出现MySql SQL_CALC_FOUND_ROWS问题

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.

它返回183行。

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.

返回8行，就像我想要显示每页8项一样。

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.

它返回20个条目，而不是原始查询中的183个。我不确定是什么引起的。你能帮我一下吗?

Thanks.

谢谢。

2 个解决方案

#1

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)

所以你的想法是使用LIMIT $begin，$number_results，当你点击next时它会取number_result + total_result_per_page并把它放到$begin中。假设你在第3页，每页显示10个结果($begin=10+10+10=30)

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

选择.....在....订单……限制30日10

EDI:

艾迪:

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

found_rows将从表的开头返回到您的极限，

 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

它是12，因为显示了10个结果+ id 1和2 + 10个结果

#2

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;