MySQL Forums
Forum List  »  Newbie

SELECT count(*) gives zero on innodb table with 400,000 rows
Posted by: Klas Adergaard
Date: June 30, 2015 05:09AM

have quite a wide (many columns) table with some 400,000 rows in it, InnoDb, UTF8MB4_Unicode, some 60 columns and 11 indexes.

See definition below.

When I do a SELECT count(*) FROM table_name WHERE user_id = X it gives correct count only for users that doesn't have that many rows in the table.

If I do RECREATE + ANALYZE i.e. what MySQL does when you perform OPTIMIZE TABLE on an InnoDb table, it works fine the count shows correct values, until we insert new rows in the table again, and then the count is off.

Am I missing some "common knowledge" about SELECT count here or is this a bug in the latest MySQL release or is it due to my table having a lot of indexes or.... something else. I've never come across this before upgrading to 5.6.25.

Thank you very much for taking the time to answer.

CREATE TABLE `table_name` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`col1` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col2` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`title` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col4` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`col5` bigint(20) NOT NULL DEFAULT '0' ,
`col6` bigint(20) NOT NULL DEFAULT '0',
`col7` tinyint(1) NOT NULL DEFAULT '1' ,
`col8` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
`col9` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col10` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col11` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col12` int(10) unsigned NOT NULL DEFAULT '0',
`col13` int(10) unsigned NOT NULL DEFAULT '0',
`col14` bigint(20) NOT NULL DEFAULT '0' ,
`col15` int(10) unsigned NOT NULL DEFAULT '0',
`user_id` bigint(20) NOT NULL DEFAULT '0' ,
`col17` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`col18` tinyint(1) NOT NULL DEFAULT '0' ,
`col19` datetime NOT NULL DEFAULT '2002-01-01 00:00:00',
`col20` tinyint(1) NOT NULL DEFAULT '0' ,
`col21` tinyint(1) NOT NULL DEFAULT '0' ,
`col22` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col23` tinyint(1) NOT NULL DEFAULT '0' ,
`col24` tinyint(1) NOT NULL DEFAULT '0' ,
`col25` tinyint(1) NOT NULL DEFAULT '0' ,
`col26` tinyint(1) NOT NULL DEFAULT '0' ,
`col27` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col28` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col29` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'NONE',
`col30` datetime NOT NULL DEFAULT '2002-01-01 00:00:00',
`col31` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`col32` bigint(20) NOT NULL DEFAULT '0',
`col33` tinyint(1) NOT NULL DEFAULT '0',
`col34` bigint(20) NOT NULL DEFAULT '0',
`col35` bigint(20) NOT NULL DEFAULT '0',
`col36` bigint(20) NOT NULL DEFAULT '0',
`col37` bigint(20) NOT NULL DEFAULT '0',
`col38` bigint(20) NOT NULL DEFAULT '0' ,
`col39` bigint(20) NOT NULL DEFAULT '0' ,
`col40` datetime NOT NULL DEFAULT '2002-01-01 00:00:00',
`col41` bigint(20) NOT NULL DEFAULT '0' ,
`col42` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Anonymous',
`col43` tinyint(1) NOT NULL DEFAULT '1' ,
`col44` int(10) NOT NULL DEFAULT '0' ,
`col45` tinyint(4) NOT NULL DEFAULT '50',
`col46` tinyint(1) NOT NULL DEFAULT '1',
`col47` tinyint(1) NOT NULL DEFAULT '0',
`col48` tinyint(1) NOT NULL DEFAULT '0',
`col49` tinyint(1) NOT NULL DEFAULT '0',
`col50` tinyint(1) NOT NULL DEFAULT '1',
`col51` int(10) NOT NULL DEFAULT '0',
`col52` tinyint(1) NOT NULL DEFAULT '0',
`col53` int(10) NOT NULL DEFAULT '0',
`col54` tinyint(1) NOT NULL DEFAULT '0',
`col55` datetime NOT NULL DEFAULT '2002-01-01 00:00:00',
`col56` bigint(20) NOT NULL DEFAULT '0',
`col57` float NOT NULL DEFAULT '0',
`col58` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `index1` (`col1`),
KEY `index2` (`user_id`),
KEY `index3` (`col10`,`col11`,`col12`),
KEY `index4` (`col13`,`col14`,`col15`),
KEY `index5` (`col16`,`col17`,`col18`),
KEY `index6` (`col19`,`col20`,`col21`),
KEY `index7` (`col22`,`col23`,`col24`),
KEY `index8` (`title`),
KEY `index9` (`col12`,`col13`,`col14`,`col15`,`col16`),
KEY `index10` (`col31`,`col32`,`col33`,`col34`,`col35`),
KEY `index11` (`created_date`),
FULLTEXT KEY `title_full` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Options: ReplyQuote


Subject
Written By
Posted
SELECT count(*) gives zero on innodb table with 400,000 rows
June 30, 2015 05:09AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.