Search code examples
mysqlsqlheidisql

Discrepance in MySQL queries between EXPLAIN, count(1) and table content

I'm analysing the performance of some queries using HeidiSQL and I've encountered this: I've found that the amount of rows returned by the EXPLAIN command is not the same as the rows that the table contains.

Queries:

  1. EXPLAIN SELECT count(1) FROM Dummy; Result:

    • select_type: SIMPLE
    • table: Dummy
    • type: index
    • key: idxF2F3
    • key_len: 10
    • rows: 2183
    • extra: Using index

  2. EXPLAIN SELECT * FROM Dummy; Result:

    • select_type: SIMPLE
    • table: Dummy
    • type: ALL
    • rows: 2183

  3. SELECT count(1) FROM Dummy; Result: 2195

  4. SELECT * FROM Dummy; Result: table of 15 columns x 2195 rows

I also used the command

select field1 count(\*)
  from table\_name
  group by field1
  having count(\*) > 1

for the keys of the table (f2 and f3) and it gives no duplicates for f3. For f2 it returns that there are 115, 53, 565, 82, 35, 37, 314, 196, 41, 118, 76, 29, 158, 68 and 308 duplicates for some rows, but there is nothing wrong here.

The table is defined by this:

CREATE TABLE Dummy(
`f1` INT(11) NULL DEFAULT NULL,
`f2` INT(11) NULL DEFAULT NULL,
`f3` INT(11) NULL DEFAULT NULL,
`f4` VARCHAR(254) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`f5` VARCHAR(254) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`f6` VARCHAR(8) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
`f7` DATETIME NULL DEFAULT NULL,
`f8` BIGINT(20) NULL DEFAULT NULL,
`f9` BIGINT(20) NULL DEFAULT NULL,
`f10` DATETIME NULL DEFAULT NULL,
`f11` DATETIME NULL DEFAULT NULL,
`f12` BIGINT(20) NULL DEFAULT NULL,
`f13` DOUBLE NULL DEFAULT NULL,
`f14` INT(11) NULL DEFAULT NULL,
`f15` INT(11) NULL DEFAULT NULL
INDEX `idxF2F3` (`f2`, `f3`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

Any help? I've searched for any similar questions

Solution

  • EXPLAIN operations reveal some of the logic in the query planner. The query planner uses table statistics to figure out how to satisfy the query, not the raw data itself.

    Table statistics can be out of date, especially on an active or recently change database.

    You can bring the statistics up to date with the ANALYZE TABLE command. In your case you would issue this command

         ANALYZE TABLE Dummy;

    to get the latest counts into your statistics.

    Pro tip: Don't worry about this sort of small discrepancy except maybe right after a massive bulk upload of table data, or right after a TRUNCATE TABLE operation.