71–80 of 86 posts
Earlier quoted context omitted.
Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.
Makes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.
Once data is cached, using indexed lookups are fast, 0.5ms.
Earlier quoted context omitted.
Is that web scale?
You run MongoDB as a backing store. Very web much scale.
Earlier quoted context omitted.
Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.
Makes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.
Earlier quoted context omitted.
Makes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.
Many DB systems have some sort of HLL function to provide a similar approximation (although I think you’re overestimating the costs that MVCC impose on large datasets).
Earlier quoted context omitted.
Generally speaking, why does SELECT COUNT(*) takes so much? I'd expect that the database maintains internal bookkeeping structures with table metadata that contain the number of rows in each table. I reckon this is probably not true? If so, is it because it keeping a counter like that up-to-date would be inefficient Edit: I just realized I might be misunderstanding what that query does
Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.
Earlier quoted context omitted.
Agreed. I have a handful of tables with over 2 billion rows and have experienced no issues. One database is 2.5TB in size. I feel like 20 million rows is a relatively small MySQL table.
What types of data with over two billion rows?
To get this info on your own mysql db: `select * from information_schema.TABLES;`. As previously disclaimed, the TABLE_ROWS here are an estimate generally, see https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.7/en/in...
(same in MySQL 8; we use both 5.7 and 8)
Earlier quoted context omitted.
Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.
Makes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.
Earlier quoted context omitted.
Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.
Makes me wonder if anybody would find a SELECT APPROXIMATELY COUNT(*) useful, which would ignore the impact of current transactions.
Earlier quoted context omitted.
Learn about MVCC storage. You might have multiple running concurrent transactions. Which is the "true" row count? Hint: you have to count.
Seems like you could just count the number of primary key entries in the index.
Meanwhile the primary key is typically the largest index, since with InnoDB's clustered index design, the primary key is the table. So it's usually not the best choice for counting unless there are no secondary indexes.
As other commenters mentioned, the query also must account for MVCC, which means properly counting only the rows that existed at the time your transaction started. If your workload has a lot of UPDATEs and/or DELETEs, this means traversing a lot of old row versions in UNDO spaces, which makes it slower.