Re: Make optimized LEFT JOIN with GROUP BY

[Björn Steinbrink]

You can use SQL_CALC_FOUND_ROWS instead, MySQL will then calculate how many rows there were without a LIMIT clause. Then you can fetch that value using FOUND_ROWS().

mysql> select * from b;
+------+------+
| id | cat |
+------+------+
| 1 | 1 |
| 1 | 4 |
| 2 | 1 |
| 3 | 4 |
| 3 | 10 |
+------+------+
5 rows in set (0.00 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM b WHERE cat = 4 LIMIT 1;
+------+------+
| id | cat |
+------+------+
| 1 | 4 |
+------+------+
1 row in set (0.00 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 2 |
+--------------+