我正在用mysql建立一个基于php的搜索。
但是我遇到了这个问题:我有一个很长很重的(mysql服务器上)查询搜索。
要对结果进行分页,我需要以下数据:
-
所有结果的计数(不限制,所以我知道有多少数字显示)
SELECT COUNT(*) as count FROM table WHERE col="val"
-
有限的结果-我将这些显示给用户
SELECT * FROM table WHERE col="val" LIMIT 20, 10
我如何避免对上面提到的2个需求有2个单独的查询?
注意: 为了避免"你的查询有多重?",这是我的真实where条件:
SELECT *,
(1000 * 6371 * acos( cos( radians(42.6051862) ) * cos( radians( project_lat ) ) * cos( radians( project_lng ) - radians(23.037836800000036) ) + sin( radians(42.6051862) ) * sin( radians( project_lat ) ) ) ) AS distance
FROM `projects`
JOIN `subcategories` ON `projects`.`subcat_id` = `subcategories`.`subcat_id`
JOIN `categories` ON `subcategories`.`cat_id` = `categories`.`cat_id`
JOIN `project_images` ON `projects`.`project_id` = `project_images`.`project_id`
WHERE `projects`.`subcat_id` = 618
AND `project_start` < '2016-10-30'
AND `project_start` > '2016-10-14'
AND `project_price` > 123
AND `project_price` < 233
AND `project_currency` = 'BTN'
AND `project_approved` = 1
AND MATCH (project_title, project_description) AGAINST ('test')
GROUP BY `projects`.`project_id`
HAVING `distance` < 7400
使用sql_calc_found_rows
SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();