Getting total number of records from mysql table - too slow

Question

I have a file that goes thru a large data set and splits out the rows in a paginated manner. The dataset contains about 210k rows, which isn't even that much, it will grow to 3Mil+ in a few weeks, but its already slow.

I have a first query that gets the total number of items in the DB for a particular WHERE clause combination, the most basic one looks like this:

SELECT count(v_id) as num_items FROM versions 
WHERE v_status = 1

It takes 0.9 seconds to run.

The 2nd query is a LIMIT query that gets the actual data for that page. This query is really quick. (less than 0.001 s).

SELECT 
        v_id, 
        v_title,
        v_desc
    FROM versions 
    WHERE v_status = 1 
    ORDER BY  v_dateadded DESC 
    LIMIT 0, 25

There is an index on v_status, v_dateadded

I use php. I cache the result into memcace, so subsequent requests are really fast, but the first request is laggy. Especially once I throw in a fulltext search in there, it starts taking 2-3 seconds for the 2 queries.

Answer 1

Do you have any indexes? Especially for v_status?

Answer 2

Not sure if this is the same for MySQL, but in MS SQL Server COUNT(*) is almost always faster than COUNT(column). The parser determines the fastest column to count and uses that.

Answer 3

I don't think this is right, but try making it count(*), i think the count(x) has to go through every row and count only the ones that don't have a null value (so it has to go through all the rows)

Given that v_id is a PRIMARY KEY it should not have any nulls, so try count(*) instead...

But i don't think it will help since you have a where clause.

Answer 4

Run an explain plan to see how the optimizer is running your queries.

That'll probably tell you what Andreas Rehm told you: you'll want to add indices that cover your where clauses.