Skip to main content
Share Your Experience: Take the 2024 Developer Survey
40 votes
Accepted

MySQL - Difference between using count(*) and information_schema.tables for counting rows

There are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable ...
ypercubeᵀᴹ's user avatar
30 votes
Accepted

Why is count(*) slow, when explain knows the answer?

Explain is using previously gathered statistics (used by the query optimizer). Doing a select count(*) reads EVERY data block. Here's a cheap way to get an estimated row count: SELECT table_rows FROM ...
Kevin Bott's user avatar
22 votes
Accepted

Why is this sqlite query much slower when I index the columns?

In SQLite, joins are executed as nested loop joins, i.e., the database goes through one table, and for each row, searches matching rows from the other table. If there is an index, the database can ...
CL.'s user avatar
  • 5,233
22 votes
Accepted

SQL counting distinct over partition

This is how I'd do it: SELECT * FROM #MyTable AS mt CROSS APPLY ( SELECT COUNT(DISTINCT mt2.Col_B) AS dc FROM #MyTable AS mt2 WHERE mt2.Col_A = mt....
Erik Darling's user avatar
  • 41.1k
14 votes
Accepted

Speeding up Count(*) on large tables

The indexed view should be among the fastest options, with the lowest maintenance overhead, when implemented optimally. Modifications are incremental (deltas) as I explain in detail in Indexed View ...
Paul White's user avatar
  • 85.8k
13 votes
Accepted

Carry over long sequence of missing values with Postgres

Form groups with the window function count() and then take the first value in each group: SELECT foo_label , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS ...
Erwin Brandstetter's user avatar
11 votes

Count with where clause

No, the syntax you have is not valid, it can be corrected by the use of a CASE expression. (and I guess you have a GROUP BY a, b as you'd get an error otherwise). select a b, count(case when ...
ypercubeᵀᴹ's user avatar
11 votes

Postgresql extremely slow count (with index, simple query)

Fetching and counting 5 million rows is slow business. There are two problems: The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with ...
Laurenz Albe's user avatar
9 votes
Accepted

count multiple occurences of attribute values for each row in sql

select m1.id, m1.attribute, (select count(*) from mytable m2 where m2.attribute = m1.attribute) from mytable m1 ; Another version: select m1.id, m1.attribute, m2.c from mytable m1 ...
Balazs Papp's user avatar
  • 40.7k
9 votes
Accepted

Can PostgreSQL use indexes to expedite count(distinct) queries?

There is a separate entry in a B-tree index for every row, for duplicates, too. So we can never "simply count the number of rows in the index". There are as many index rows to read and ...
Erwin Brandstetter's user avatar
9 votes
Accepted

SQL Return Table with where clause and occurrences of value of total table

You can use a windowed COUNT inside a derived table (subquery) SELECT * FROM ( SELECT *, Total = COUNT(*) OVER (PARTITION BY t.RouteId) FROM MyTable t ) t WHERE t.Name = 'Bob'; db<&...
Charlieface's user avatar
  • 13.2k
8 votes
Accepted

How do you count the occurrences of an anchored string using PostgreSQL?

You can solve this with a FASTEST was the pl/perl method which I placed last on this list because it requires pl/perl, and is likely not needed for most workloads. FAST A string function, such as one ...
Evan Carroll's user avatar
  • 63.6k
8 votes
Accepted

Select unique value pairs that occur (count) more than once inside a table

What you have almost works, just remove the distinct and change the > 2 to > 1. The distinct is not necessary as the grouping handles that and the > 2 is looking for things that have at least three ...
Leigh Riffel's user avatar
  • 23.9k
8 votes
Accepted

Counting unique (distinct) users per day

Because your form is off, what you want is SELECT count(DISTINCT x) FROM generate_series(1,10) AS gs(x); Or, in your case, SELECT start_time::date, count(DISTINCT user_id) FROM mytable GROUP BY ...
Evan Carroll's user avatar
  • 63.6k
8 votes

Count items on condition

Use the modern aggregate FILTER syntax in Postgres 9.4 or later: SELECT userid, COUNT(*) FILTER (WHERE version_identifier LIKE '%ios%') AS nr_ios, COUNT(*) FILTER (WHERE ...
Erwin Brandstetter's user avatar
8 votes
Accepted

Simultaneous aggregate count and full count

What you want is ROLLUP: SELECT COUNT(DISTINCT correlation_id), type FROM events WHERE type IN (3, 5, 97, 98) GROUP BY ROLLUP (type);
mustaccio's user avatar
  • 26.2k
7 votes

How to use COUNT with multiple columns?

Obviously, COUNT(DISTINCT) with multiple columns counts unique combinations of the specified columns' values. However, one other important point is that a tuple is counted only if none of the ...
Andriy M's user avatar
  • 23k
7 votes

SQL counting distinct over partition

You can emulate it by using dense_rank, and then pick the maximum rank for each partition: select col_a, col_b, max(rnk) over (partition by col_a) from ( select col_a, col_b , dense_rank()...
Lennart - Slava Ukraini's user avatar
7 votes

SQL counting distinct over partition

This is, in a way, an extension to Lennart's solution, but it is so ugly that I dare not suggest it as an edit. The goal here is to get the results without a derived table. There may never be the need ...
Andriy M's user avatar
  • 23k
7 votes

Postgresql extremely slow count (with index, simple query)

TL;DR I have solved by running this command: vacuum analyze subscriptions; After that command the queries take only ~1s instead of ~17s. For a detailed explanation see @Laurenz answer. Now I run the ...
collimarco's user avatar
6 votes
Accepted

Count per week per column PostgreSQL

As count() counts only the non-NULL values, this is a simple GROUP BY query: SELECT week, count(col1), ..., count(col10) FROM your_table GROUP BY week;
András Váczi's user avatar
6 votes

Join Multiple Tables for Aggregates

This request is quite old, but as the accepted answer is wrong, I thought I'd add a correct one, so future readers don't get too confused. A campain has landers and conversions. If we merely join all ...
Thorsten Kettner's user avatar
6 votes

Speeding up Count(*) on large tables

If you're stuck on SQL Server 2012 you could try creating an index on just the clustered index key. It might be a little smaller than an index on a TINYINT column. You could also try adding page ...
Joe Obbish's user avatar
  • 32.2k
6 votes
Accepted

Count all rows for each DISTINCT

In this case you can use GROUP BY to get distinct column1 values, and instead of convert the date to text you can use EXTRACT function for this purpose. select colum1, count(*) as ...
McNets's user avatar
  • 23.8k
6 votes

SQL Return Table with where clause and occurrences of value of total table

Three more alternatives: Subquery SELECT MT.*, Total = ( SELECT COUNT_BIG(*) FROM dbo.MyTable AS MT2 WHERE MT2.RouteId = MT....
Paul White's user avatar
  • 85.8k
6 votes
Accepted

Optimizing select count result in Postgresql

From the PostgreSQL Wiki: The reason is related to the MVCC implementation in PostgreSQL. The fact that multiple transactions can see different states of the data means that there can be no ...
John K. N.'s user avatar
  • 17.9k
6 votes

How to write an SQL query where the count of 2 different attributes is the same?

Q: List the names of all actors who acted in an equal number of comedies and tragedies. You can accomplish this with a single query a few different ways, no need for creating Views. One way is with a ...
J.D.'s user avatar
  • 37.9k
5 votes

How to COUNT number of rows with LIMIT?

I know this is an old one but it is not valid anymore, with MySQL >= 8.0.17 cf MySQL Function Found_Rows() So we still have to issue both queries ie. add one with the COUNT(), unfortunately.
Fred's user avatar
  • 151
5 votes

MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed

The way that both RDBMS do the count differs. In InnoDB we have the following behaviour by default: To process a SELECT COUNT(*) FROM t statement, InnoDB scans an index of the table, which takes ...
3manuek's user avatar
  • 1,449
5 votes

Speed up count queries on a couple million rows

While computing numbers for all or most users, it's much more efficient to use plain subqueries to aggregate counts per user before joining instead of correlated subqueries: SELECT u.*, p....
Erwin Brandstetter's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible