4 ms·
Just keep in mind SELECT * and COUNT(*) are only visually similar. The first one needs to read all data if used at the top level and the second one needs no ac
by cube2222 5y ago
Just keep in mind SELECT * and COUNT(*) are only visually similar.
The first one needs to read all data if used at the top level and the second one needs no actual data - just the overall row count, in whatever way is most optimal to calculate.
- booi 5y agoI think this belief is a remnant of old versions of MySQL which traversed the entire table counting rows. This is mostly in the past though as they'll typically use the primary key index to count rows or just save the number somewhere (depending on the DBMS)
- alecst 5y agoDo you have a source for this? This is relevant to my work, but I don't know much about the implementation details of MySQL.
- evanelias 5y agoInnoDB (the most common storage engine in MySQL) uses a clustered index. In other words, the "primary key index" IS the table. Also a COUNT(*) query has to account for MVCC, ensuring that the count represents an accurate total for a specific isolated snapshot. So this is basically still true today, although MySQL 8 has started to add parallelized reads to improve performance for this situation.