CODEWITHSUNDEEP
mysqlsqlperformancequery-optimization
George
George

Reputation: 3953

Optimize SQL to get rows count

I have a page on my site that keeps track of the number of people accessing it, on another part I displays the data containing information about the users that access this page, it displays only about 10 at a time.

The problem is I need to create pagination so I need to know how much data is on my table at every time and this causes the display page to take some time to load 2-3 seconds, sometimes 7-10, because I have millions of record. I am wondering, how do I get this page to load faster.

Select COUNT(*) as Count from visits

Upvotes: 2

Views: 118

Answers (5)

Sergiy Tytarenko
Sergiy Tytarenko

Reputation: 482

Decide on limit (let's say, 1000 last ones) from practical (business requirements) point of view. Have auto_increment index (id) or timestamp (createdon). Grab max 1000 records

select count(*) from (select id from visits order by id desc limit 1000)

or grab all 1000 and count paginate on the client side (php) (as if you paginate mysql will still go through those records):

select * from visits order by id desc limit 1000

Upvotes: 0

Rick James
Rick James

Reputation: 142528

The best answer is to say "About 1,234,000 visits", not the exact number. Then calculate it daily (or whatever).

But if you must have the exact count, ...

If this table is "write only", then there is a solution. It involves treating it as a "Fact" table in a Data Warehouse. Then create and maintain a "Summary table" with a row for, say, each hour. Then the COUNT becomes:

SELECT SUM(hourly_count) FROM SummaryTable;

This will be much faster because there is much less to scan. However, there is a problem in that it does not include the count for the last (partial) hour. But that can be solved if you use INSERT ... ON DUPLICATE KEY UPDATE ... to increment the counter for the current hour or insert a new row with a "1".

Some more info is here .

But, before we take this too far, please inform us of how often a new 'visit' occurs.

Upvotes: 1

Antonín Lejsek
Antonín Lejsek

Reputation: 6103

You usually do not need to know exact number of rows for pagination

SELECT COUNT(*) FROM
(SELECT TOP 10000 * FROM visits) as v

would tell You, that there are at least 1000 pages. In most cases You do not need to know more.

You can store total count somewhere and update it from time to time if You want some reasonable estimate. If You need exact number, You can use trigger to keep it actual. The more up to date info, the more expensive, of course.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

My first response is . . . if you are paging records 10 at a time, why do you need the total count of more than a million?

Second, counting a million rows should not take very long, unless your rows are wide (lots of columns or wide columns). If that is the case, then:

select count(id) from t;

can help, because it will explicitly use an index. Note that the first run may be slower than subsequent runs because of caching.

If you decide that you do need an exact row count, then your only real option for speeding it up using MySQL is to create triggers to maintain the count in another table. However, that will slow down inserts and deletions, which might not be a good idea.

Upvotes: 1

Mihai Popescu
Mihai Popescu

Reputation: 396

You cannot make that query get faster without changing the server's hardware or adding more servers to run it in parallel. In the second case it would be better to move to a nosql database.

My approach would be to reduce the number of records. That you could do by having some temporary table where you record the access logs for the past hour/day and after that time run a cronjob that deletes the data, or moves it to another table for log term storage.

Upvotes: 0

Related Questions