Edit File: select_found.result
drop table if exists t1,t2; create table t1 (a int not null auto_increment, b int not null, primary key(a)); insert into t1 (b) values (2),(3),(5),(5),(5),(6),(7),(9); select SQL_CALC_FOUND_ROWS * from t1; a b 1 2 2 3 3 5 4 5 5 5 6 6 7 7 8 9 select found_rows(); found_rows() 8 select SQL_CALC_FOUND_ROWS * from t1 limit 1; a b 1 2 select found_rows(); found_rows() 8 select SQL_BUFFER_RESULT SQL_CALC_FOUND_ROWS * from t1 limit 1; a b 1 2 select found_rows(); found_rows() 8 select SQL_CALC_FOUND_ROWS * from t1 order by b desc limit 1; a b 8 9 select found_rows(); found_rows() 8 select SQL_CALC_FOUND_ROWS distinct b from t1 limit 1; b 2 select found_rows(); found_rows() 6 select SQL_CALC_FOUND_ROWS b,count(*) as c from t1 group by b order by c desc limit 1; b c 5 3 select found_rows(); found_rows() 6 select SQL_CALC_FOUND_ROWS * from t1 left join t1 as t2 on (t1.b=t2.a) limit 2,1; a b a b 3 5 5 5 select found_rows(); found_rows() 8 drop table t1; create table t1 (a int not null primary key); insert into t1 values (1),(2),(3),(4),(5); select sql_calc_found_rows a from t1 where a in (1,2,3) order by a desc limit 0,2; a 3 2 select FOUND_ROWS(); FOUND_ROWS() 3 select sql_calc_found_rows a from t1 where a in (1,2,3) order by a+2 desc limit 0,2; a 3 2 select FOUND_ROWS(); FOUND_ROWS() 3 drop table t1; CREATE TABLE t1 ( `id` smallint(5) unsigned NOT NULL auto_increment, `kid` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`), KEY `kid` (`kid`) ); CREATE TABLE t2 ( id smallint(5) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', email varchar(50) NOT NULL default '', PRIMARY KEY (id), UNIQUE KEY e_n (email,name) ); EXPLAIN SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY,kid NULL NULL NULL 0 const row not found 1 SIMPLE t2 index NULL e_n 104 NULL 10 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 email10 email100 email101 email102 email103 email104 email105 email106 email107 SELECT FOUND_ROWS(); FOUND_ROWS() 200 SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL order by email LIMIT 10; email email1 email10 email100 email101 email102 email103 email104 email105 email106 email107 SELECT FOUND_ROWS(); FOUND_ROWS() 200 SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 email2 email3 email4 email5 email6 email7 email8 email9 email10 SELECT DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL ORDER BY email LIMIT 10; email email1 email10 email100 email101 email102 email103 email104 email105 email106 email107 INSERT INTO `t1` (`id`, `kid`) VALUES ('0', '150'); SELECT SQL_CALC_FOUND_ROWS DISTINCT email FROM t2 LEFT JOIN t1 ON kid = t2.id WHERE t1.id IS NULL LIMIT 10; email email1 email2 email3 email4 email5 email6 email7 email8 email9 email10 SELECT FOUND_ROWS(); FOUND_ROWS() 199 drop table t1,t2; CREATE TABLE `t1` ( `titre` char(80) NOT NULL default '', `numeropost` mediumint(8) unsigned NOT NULL auto_increment, `maxnumrep` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`numeropost`), KEY `maxnumrep` (`maxnumrep`) ) ENGINE=MyISAM ROW_FORMAT=FIXED; INSERT INTO t1 (titre,maxnumrep) VALUES ('test1','1'),('test2','2'),('test3','3'); SELECT SQL_CALC_FOUND_ROWS titre,numeropost,maxnumrep FROM t1 WHERE numeropost IN (1,2) ORDER BY maxnumrep DESC LIMIT 0, 1; titre numeropost maxnumrep test2 2 2 SELECT FOUND_ROWS(); FOUND_ROWS() 2 SELECT SQL_CALC_FOUND_ROWS 1 FROM (SELECT 1) as a LIMIT 0; 1 SELECT FOUND_ROWS(); FOUND_ROWS() 1 SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE numeropost > 1 LIMIT 0; titre numeropost maxnumrep SELECT FOUND_ROWS(); FOUND_ROWS() 2 SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 0; titre numeropost maxnumrep SELECT FOUND_ROWS(); FOUND_ROWS() 3 SELECT SQL_CALC_FOUND_ROWS * FROM t1 ORDER BY numeropost LIMIT 0; titre numeropost maxnumrep SELECT FOUND_ROWS(); FOUND_ROWS() 3 drop table t1; create table t1 (id int, primary key (id)); insert into t1 values (1), (2), (3), (4), (5); select SQL_CALC_FOUND_ROWS * from t1 where id > 3 limit 0, 1; id 4 select FOUND_ROWS(); FOUND_ROWS() 2 select SQL_CALC_FOUND_ROWS * from t1 where id > 3 AND 1=2 limit 0, 1; id select FOUND_ROWS(); FOUND_ROWS() 0 select SQL_CALC_FOUND_ROWS * from t1 where id > 6 limit 0, 1; id select FOUND_ROWS(); FOUND_ROWS() 0 drop table t1; CREATE TABLE t1 ( a int not null, b int not null, KEY ab(a,b) ); INSERT INTO t1 VALUES ( 47, 1 ); INSERT INTO t1 VALUES ( 70, 1 ); SELECT * FROM t1 WHERE ( ( b =1 AND a BETWEEN 14 AND 21 ) OR ( b =2 AND a BETWEEN 16 AND 18 ) OR ( b =3 AND a BETWEEN 15 AND 19 ) ); a b DROP TABLE t1; CREATE TABLE t1 ( a integer, u varchar(15), r integer, key uao_idx( r, a, u)); DELETE FROM t1 WHERE ( r = 1 AND a IN ( 1, 2 ) AND ( u = 'w' OR u LIKE 'w/%' ) ) OR ( r = 1 AND a IN ( 3 ) AND ( u = 'w/U' OR u LIKE 'w/U/%' ) ) OR ( r = 1 AND a IN ( 1, 2, 3 ) AND ( u = 'w' ) ); drop table t1; CREATE TABLE t1 (a VARCHAR(16), UNIQUE(a)); INSERT INTO t1 VALUES ('1'), ('2'), ('3'); SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = '2' LIMIT 0, 1; a 2 SELECT FOUND_ROWS(); FOUND_ROWS() 1 DROP TABLE t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (0), (0), (1), (2); SELECT SQL_CALC_FOUND_ROWS * FROM t1 WHERE a = 0 GROUP BY a HAVING a > 10; a SELECT FOUND_ROWS(); FOUND_ROWS() 0 DROP TABLE t1; SELECT 'foo'; foo foo SELECT FOUND_ROWS(); FOUND_ROWS() 1 SELECT SQL_CALC_FOUND_ROWS 'foo'; foo foo SELECT FOUND_ROWS(); FOUND_ROWS() 1 SELECT SQL_CALC_FOUND_ROWS 'foo' limit 0; foo SELECT FOUND_ROWS(); FOUND_ROWS() 1 SELECT FOUND_ROWS(); FOUND_ROWS() 1 SELECT SQL_CALC_FOUND_ROWS 'foo' UNION SELECT 'bar' LIMIT 0; foo SELECT FOUND_ROWS(); FOUND_ROWS() 2 CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,2), (1,3), (1,4), (1,5); SELECT SQL_CALC_FOUND_ROWS DISTINCT 'a' FROM t1 GROUP BY b LIMIT 2; a a SELECT FOUND_ROWS(); FOUND_ROWS() 1 DROP TABLE t1;
Back to File Manager