CODEWITHSUNDEEP
mysqlsqlcount
David542
David542

Reputation: 110093

Doing a more efficient COUNT

I have a page that loads some high-level statistics. Nothing fancy, just about 5 metrics. There are two particular queries that takes about 5s each to load:

+ SELECT COUNT(*) FROM mybooks WHERE book_id IS NOT NULL
+ SELECT COUNT(*) FROM mybooks WHERE is_media = 1

The table has about 500,000 rows. Both columns are indexed.

This information changes all the time, so I don't think that caching here would work. What are some techniques to use that could speed this up? I was thinking:

What would be suggested here? The requirement is that the page loads within 1s.

Table structure:

Upvotes: 1

Views: 320

Answers (2)

Voidmain
Voidmain

Reputation: 141

The stats table is probably the biggest/quickest bang for buck. Assuming you have full control of your MySQL server and don't already have job scheduling in place to take care of this, you could remedy this by using the mysql event scheduler. As Vlad mentioned above, your data will be a bit out of date. Here is a quick example:

Example stats table

CREATE TABLE stats(stat VARCHAR(20) PRIMARY KEY, count BIGINT);

Initialize your values

INSERT INTO stats(stat, count)
VALUES('all_books', 0), ('media_books', 0);

Create your event that updates every 10 minutes

DELIMITER |

CREATE EVENT IF NOT EXISTS updateBookCountsEvent
ON SCHEDULE EVERY 10 MINUTE STARTS NOW()
COMMENT 'Update book counts every 10 minutes'
DO
BEGIN
    UPDATE stats
    SET count = (SELECT COUNT(*) FROM mybooks)
    WHERE stat = 'all_books';
    UPDATE stats
    SET count = (SELECT COUNT(*) FROM mybooks WHERE is_media = 1)
    WHERE stat = 'media_books';
END |

Check to see if it executed

SELECT * FROM mysql.event;

No? Check to see if the event scheduler is enabled

SELECT @@GLOBAL.event_scheduler;

If it is off you'll want to enable it on startup using the param --event-scheduler=ON or setting it in you my.cnf. See this answer or the docs.

Upvotes: 1

Pattle
Pattle

Reputation: 6016

There are a couple of things you can do to speed up the query.

  1. Run optimize table on your mybooks table

  2. Change your book_id column to be an int unsigned, which allows for 4.2 billions values and takes 4 bytes instead of 8 (bigint), making the table and index more efficient.

Also I'm not sure if this will work but rather than doing count(*) I would just select the column in the where clause. So for example your first query would be SELECT COUNT(book_id) FROM mybooks WHERE book_id IS NOT NULL

Upvotes: 1

Related Questions