Bug #18454 SQL_CALC_FOUND_ROWS should calculate rows during tmp table builds/filesorts
| Submitted: | 23 Mar 2006 12:26 | Modified: | 25 Jun 2011 0:48 |
|---|---|---|---|
| Reporter: | Domas Mituzas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 4.1, 5.0, 5.1 | OS: | Linux (Linux) |
| Assigned to: | Evgeny Potemkin | CPU Architecture: | Any |
[23 Mar 2006 12:26]
Domas Mituzas
[17 Aug 2006 11:56]
Kay Röpke
A related case just bit me: Suppose you do: select sql_calc_found_rows field_in_index, not_in_index from tab where field_in_index = 42 and type_in_index=23 order by date_in_index desc limit 0,10; For potentially big result sets (which are clipped by limit) this results in a read of all rows in order to calculate the found_rows instead of simply counting the matching index values. In these cases it's better to actually do a separate select count(*) query afterwards, even though that might imply having to lock. It's several magnitudes faster.
[13 Jan 2007 5:22]
Guillaume Boissiere
On a table with 50,000 records, I found that doing: SELECT count(0) FROM mytable WHERE ... (to get the total count) SELECT * FROM mytable WHERE ... LIMIT 50 was about 3 times faster than doing: SELECT SQL_CALC_FOUND_ROWS * FROM mytable WHERE ... LIMIT 50 SELECT FOUND_ROWS() so it is probably not worth using on large tables until this bug is fixed. Of course, you should benchmark it on your own application to be sure.
[23 Mar 2009 20:57]
bill wu
Work fine with Limit, but when I add the order by, it will be slow.
[25 Jun 2011 0:48]
Paul DuBois
Fixed in 5.6.3
[27 Jun 2011 14:28]
Paul DuBois
Correction: Fixed in 5.6.2.
[28 May 2013 16:34]
Guido Serra
not really... I just reproduced it on 5.6.10 see the perf I'm having on MariaDB... maybe u wanna backport some code ;-) - https://mariadb.atlassian.net/browse/MDEV-4592
[17 Mar 2017 19:25]
Phil Wilson
This is still a problem for me.
[17 Mar 2017 19:36]
Phil Wilson
I am using MYSQL ver 5.7.15 and still seeing this bug. Any chance it is fixed somewhere?
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
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.