MySQL: SELECT() results in less rows than COUNT()

Asked Oct 11 '19 at 11:01

Active Oct 11 '19 at 11:01

Viewed 65 times

My number of rows found with count(*) is a lot higher than the select(*). As the address is the unique index, I shouldn't have any duplicate addresses in my database. How could I find out which records are causing this?

Query 1:

Showing rows 0 - 24 (4613986 total, Query took 0.0004 seconds.)
SELECT * FROM `addresses`

Query 2:

Your SQL query has been executed successfully.
SELECT COUNT(*) FROM addresses 

COUNT(*)    
4647734

My table:

CREATE TABLE `addresses` (
  `address` varchar(40) NOT NULL,
  `referral` varchar(40) NOT NULL,
  `wallet` varchar(30) DEFAULT NULL,
  `tovisit` int(3) DEFAULT '1',
  `lastvisited` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `addresses`
  ADD UNIQUE KEY `address` (`address`);

asked Oct 11 '19 at 11:01

Scripter

https://dba.stackexchange.com/questions/170911/performance-select-count-where-vs-select-column-name-where – Sachin Yadav Oct 11 '19 at 11:07
Does MySQL report errors if you run `CHECK TABLE addresses`? – Álvaro González Oct 11 '19 at 11:08
@ÁlvaroGonzález addresses check status OK – Scripter Oct 11 '19 at 11:10
5

Do not trust PHPMyAdmin's `Showing rows 0 - 24 (4613986 total, Query took 0.0004 seconds.)` message.. As the `4613986 total` is estimated.. – Raymond Nijland Oct 11 '19 at 11:14
3

You can rely on the value returned by `COUNT(*)` but not PMA. See: https://stackoverflow.com/questions/11926259/why-is-the-estimated-rows-count-very-different-in-phpmyadmin-results – Salman A Oct 11 '19 at 11:16

MySQL: SELECT(*) results in less rows than COUNT(*)

0 Answers0

MySQL: SELECT() results in less rows than COUNT()