菜单

查询语句使用SQL_CALC_FOUND_ROWS的效果

2019年6月7日 - MySQL

mysql> select SQL_CALC_FOUND_ROWS * FROM tbl_name -> WHERE id
> 100 LIMIT 10; 
mysql> select FOUND_ROWS(); 

使用SQL语句写语句询问
壹.写函数GetSalaryByNumber,接收职工编号employee_number,重临此职员和工人的薪金标准employee_salary。
亟待建表Employee,列为:employee_number:varchar,employee_salary:money。

使用SQL_CALC_FOUND_ROWS能够在询问时为你事先企图好符合where条件的笔录数据,然后借使在随着施行一句select
FOUND_ROWS(); 就会获取总记录数。

其1法子有三个副功效,当使用了SQL_CALC_FOUND_ROWS以往,将不可能采取查询缓存、在特出情状下反而或损失一些属性。

举例说,二个稿子表,全体小说做了主键ID,并做了CREATE_TIME
DESC的目录。那样在实践
SELECT    *    FROM    ARTICLE   ORDER   BY  ID DESC LIMIT 10 或者
 CREATE_TIME DESC LIMIT 20
时,数据库引擎可以完全依附目录重临最新篇章而不会管你有多少符合的记录,但用了SQL_CALC_FOUND_ROWS后引擎不得不扫描全表以明显全体记录数。

但无论怎么样,这一个 SQL_CALC_FOUND_ROWS
特别适合where字句十分复杂耗费时间的情况。在数十次利用查询的利用中,那个办法能够拉动1/三的数据库质量进步。

Never, never use SQL_CALC_FOUND_ROWS it’s just equally slow then
“SELECT COUNT(*) FROM table”, but you have to do it on every page. The
count(*) variant you can cache at last, so you don’t have to do it on
every page.
(用SQL_CALC_FOUND_ROWS 每页每一回查询都会做Count;用
count(*),则足以友善缓存结果)

And there’s even another way to avoid the count on paging. It’s the way
Facebook does paging on some places. Facebook don’t give you the usually
list of pages from 1 to n there, were you can click at any page. They
just give you the page before, the current page and the next page, if
there’s one. On the application side it’s very easy to find out if you
have a page before the current, when you are on the second page there’s
one before (so no surprise here). But what about the next page if you
don’t want to make a count. Easy stuff, let me predict you display 10
items per page, so query 11 items instead of 10 per page. This one extra
item will cost you nearly nothing and now you can count the returned
rows in your application. If you have more than ten rows you have a next
page and you can happily throw number eleven away.
(只浮现上下页;程序很轻易精通有未有上一页;鉴定下一页,只供给多询问一条,则足以决断有未有下一条记下了。)

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN
wp_term_relationships ON (wp_posts.ID =
wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON
(wp_term_relationships.term_taxonomy_id =
wp_term_taxonomy.term_taxonomy_id) LEFT JOIN wp_wti_like_post on
wp_wti_like_post.post_id=wp_posts.ID WHERE 1=1 AND ( (
post_date_gmt > ’2013-11-08 15:37:48′ ) ) AND (
wp_term_relationships.term_taxonomy_id IN (5) ) AND
wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’ OR
wp_posts.post_status = ‘private’) GROUP BY wp_posts.ID ORDER BY
wp_wti_like_post.value DESC,wp_posts.post_date DESC LIMIT 0, 2

那是一段wordpress程序生成的sql语句。。

(原作地址:http://www.tantengvip.com/2013/11/sql\_calc\_found\_rows/)

http://www.bkjia.com/Mysql/625954.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/625954.htmlTechArticlemysql select SQL_CALC_FOUND_ROWS * FROM
tbl_name – WHERE id 100 LIMIT 10; mysql select FOUND_ROWS();
使用SQL_CALC_FOUND_ROWS能够在查询时为您事先筹算好符合where条件…

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图