Getting total number of records from mysql table - too slow

I have a file that goes thru a large data set and s开发者_运维百科plits 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.

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.

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.

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.

EDIT: For me FOUND_ROWS() was the fastest way of doing this:

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

Then in a secondary query just do:

SELECT FOUND_ROWS();

If you are outputting to PHP you do this:

$totalnumber = mysql_result(mysql_query($secondquery)),0,0);

I was previously trying to the same thing as OP, putting COUNT(column) on the first query but it took about three times longer than even the slowest WHERE and ORDERBY query that I could do (with a LIMIT set). I tried changing to COUNT(*) and it improved a lot. But results in my case were even better using MySQL's FOUND_ROWS();

I am testing in PHP with microtime and repeating the query. In OP's case, if he ran COUNT(*) I think he will save some time, but it is not the fastest way of doing this. I ran some tests on COUNT(*) VS. FOUND_ROWS() and FOUND_ROWS() is quite a bit faster.

Using FOUND_ROWS() was nearly twice as fast in my case.

I first started doing EXPLAIN on the COUNT(*) query. In OP's case you would see that MySQL still checks a total of 210k rows in the first query. It checks every row before even starting the LIMIT query and doesn't seem to get any performance benefit from doing this.

If you run EXPLAIN on the LIMIT query it will probably check less than 100 rows as you have limited the results to 25. But this is still overlap and there will be some cases where you can't afford this or at the least you should still compare performance with FOUND_ROWS().

I thought this might only save time on large LIMIT requests, but when I run EXPLAIN on my LIMIT query it was actually only checking 25 rows to get 15 values. However, there was still a very noticeable difference in query time - on average I got down from .25 to .14 seconds and achieved the same results.