How to make the count(*) query faster?

Question

I have the table(named users_events) consists of below columns

date(date_time)
eventid(autoincrement field) PRIMARY KEY
two foreign keys
two long text fields
three integer fields and having the indexes on date_time column(btree) and two text fields(fulltext).

We have 14 million records in the table. When I try below query it's taking more than 30 seconds

 select count(*) from users_events

please help me out to how to reduce query time..

This is quite difficult to say...You could try if this query is faster: SELECT COUNT(eventid) FROM users_events. Furthermore, you could have a look in the query execution plan if there are some proposes like missing indexes etc. — Jonas Metzler
– Jonas Metzler, Commented Apr 25, 2022 at 14:20
What is the real purpose to have a count of over 14mil records. Just factual or what. Maybe having a separate summary table, even if by month having a count of event records pre-grouped by month/year, then it would be a simple summation of the pre-aggregates in-time, since the historical data probably is not changing. Then you only may need to run updated query for whatever the latest month is since it could keep growing as the day progresses. — DRapp
– DRapp, Commented Apr 25, 2022 at 15:25
As others have said. There's no meaningful diffemece bwtween large numbers. If you are looking for the biggest number select eventid from user_events order by eventid desc limit 1 should be really fast and bit the index — exussum
– exussum, Commented Apr 25, 2022 at 19:04
Using * can be faster than using a specific column, as a specific column will invoke the index if one exists for it, like it would for an ID, this has been benchmarked numerous times — zanderwar
– zanderwar, Commented Apr 26, 2022 at 5:32

zanderwar · Accepted Answer · 2022-04-26 07:38:27Z

2

Build and maintain a Summary Table

zanderwar

3,7793 gold badges31 silver badges51 bronze badges

answered Apr 25, 2022 at 17:47

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1