0

MySQL Version 8.0.32-0ubuntu0.20.04.2

I'm trying to improve SELECT performance, not circumvent doing a SELECT.

CREATE TABLE big_table (
pk INT AUTO_INCREMENT PRIMARY KEY,
field1 VARCHAR(255),
field2 VARCHAR(255),
field3 mediumtext,
field4 BIGINT,
KEY idx_field4 (field4)
) ENGINE=MyISAM CHARSET=utf8mb3;

Insert 15 million rows.

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (0.57 sec)

ALTER TABLE big_table ENGINE=INNODB;

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (10.23 sec)

**Set innodb_buffer_pool_size=8G (Was 128Mb) (Restarted MySQL) **

SELECT COUNT(pk) FROM big_table;
+---------------+
| count(pk)     |
+---------------+
|      15911974 |
+---------------+
1 row in set (1 min 18.67 sec)
Chris Barnes Clarumedia
  • 13
  • 4
  • 1
    Does this answer your question? [How to do a fast but innacurate InnoDB row count?](https://stackoverflow.com/questions/6082810/how-to-do-a-fast-but-innacurate-innodb-row-count) – danblack May 06 '23 at 08:24
  • Thank you for teaching me a new cool trick with: SHOW TABLE STATUS LIKE 'big_table'; However, it doesn't help me improve SELECT performance. Thank you – Chris Barnes Clarumedia May 06 '23 at 08:36
  • Can you share more details? Like do you need the `15911974`, or is "15E6" also correct? (or are you actually doing another query...) – Luuk May 06 '23 at 08:42
  • When you never deleted anything, `SELECT MAX(pk) FROM bigtable;` might be correct. – Luuk May 06 '23 at 08:46
  • Luuk. For now, that is the only query I'm doing. It DOES need to be a select for a bunch of reasons irrelevant to my question. Yes I do need the 15911974. I'm trying to solve the root problem rather than circumvent it. Thank you – Chris Barnes Clarumedia May 06 '23 at 08:50
  • Now try `COUNT(*)`. When timing something, run the query twice -- the first tends to load the cache; the seconds may not need any I/O. – Rick James May 06 '23 at 14:20
  • Sadly not. Thank you for the suggestion. I'm beginning to see that SELECT COUNT(PK) or SELECT COUNT(*) with MyISAM and no where clause was doing something fast and funky. As soon as a WHERE clause is introduced then the performance is the same for both. – Chris Barnes Clarumedia May 06 '23 at 16:52

1 Answers1

1

It turns out that SELECT COUNT(PK) or SELECT COUNT(*) on a MyISAM table with no where clause is a very specific situation because it does not count each row and so is very fast. The same query on InnoDB is slow because it DOES count each row.

However as soon as a where clause comes into play on an indexed field, the performance of InnoDB appears to outshine MyIsam. Seeing as this is 99% of use cases.....

Chris Barnes Clarumedia
  • 13
  • 4