MySQL Bugs: #25976: COUNT or SQL_CALC_FOUND

Bug #25976	COUNT or SQL_CALC_FOUND_ROWS with LEFT JOIN
Submitted:	31 Jan 2007 13:35	Modified:	6 Feb 2007 10:50
Reporter:	Hervé Guillemet	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.27	OS:
Assigned to:		CPU Architecture:	Any

Description:
The time to execute a COUNT and SQL_CALC_FOUND_ROWS depends on the presence of LEFT JOIN in the query, while a LEFT JOIN won't affect the result.

How to repeat:
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);

-- populates t1 and t2 with many rows

SELECT COUNT(*) FROM t1; -- fast
SELECT COUNT(*) FROM t1 LEFT JOIN t2 USING (id); -- slow
SELECT SQL_CALC_FOUND_ROWS id FROM t1 LIMIT 10; --fast
SELECT SQL_CALC_FOUND_ROWS id FROM t1 LEFT JOIN t2 USING (id) LIMIT 10; --slow

Thank you for a problem report. Please, send the results of:

EXPLAIN SELECT COUNT(*) FROM t1\G
EXPLAIN SELECT COUNT(*) FROM t1 LEFT JOIN t2 USING (id)\G
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id FROM t1 LIMIT 10\G
EXPLAIN SELECT SQL_CALC_FOUND_ROWS id FROM t1 LEFT JOIN t2 USING (id) LIMIT 10\G

mysql> EXPLAIN SELECT COUNT(*) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT COUNT(*) FROM t1 LEFT JOIN t2 USING (id)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 75803
        Extra: 

*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 75803
        Extra: 
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS id FROM t1 LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 75803
        Extra: 
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS id FROM t1 LEFT JOIN t2 USING (id) LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 75803
        Extra: 
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 75803
        Extra: 
2 rows in set (0.00 sec)

As you can see, number of rows that should be checked is different in fast cases (0 and 75803) and slow cases (75803 and 75803*75803, respectively). So, why do you think it is a bug? For me it is expected behaviour. Just do not use LEFT JOIN if you know for sure that other table will not add up anything significant to your results. Or, at least, create indexes on column you join on.

Of course if we add indexes in this simple case the results will be fastest.
The point I would like to make is that a LEFT JOIN does not change the row count, so it seems as a possible optimization to ignore LEFT JOIN when the engine does not need to retrieve the rows but only count them.

This need occurs in a classical situation where we want to display page per page the result of a complex query. The code that diplays the table takes an abitrary SQL rely on SQL_CALC_FOUND_ROWS to display a page count and LIMIT/OFFSET to retrieve a page.

Sorry, Submit were clicked by error too early 

... so in this situation if we have complex query with complex or wrongly optimized LEFT JOIN we end up with a very slow display of a table page, just because of the page count, while the LEFT JOIN should not affect the page count.

Feedback terminated this time :)

Left join might:

create table t1 (a int); insert t1 values (1);
create table t2 (a int); insert t2 values (1), (1);
select count(*) from t1
-- result: 1
select count(*) from t1 left join t2 using (a);
-- result: 2

Please, check example provided by Sergei. You still think it is a bug? How optimizer should check that LEFT JOIN will NOT affect the result? Without reading the other table?

Right, I missed this case. In the concerned situations we left-join using primary keys so there is no change in the result, but in the general case there might be.
Sorry about this buggy bug report, I guess you can close it.

So, this is not a bug.