Hello! I am having a problem retrieving the number of records matching a certain condition from the database. I have a large table of about 3 millions records
A simple query like the one below returns me the results select * use index(category) from books where category=1 limit 0,10 This query takes about 0.01 seconds since I have an index on the category column When I try to retrieve the number of rows matching this condition I am using one of the following 2 queries 1. select SQL_CALC_FOUND_ROWS * use index(category) from books where category=1 limit 0,10 and then I retrieve the needed result using FOUND_ROWS()... This query where I make use of SQL_CALC_FOUND_ROWS takes about 15 seconds 2. select count(*) use index(category) from books where category=1 which returns me the needed result... This query takes about 3 seconds using explain on both queries I notice that the first query is not using anymore the index and I cannot figure out exactly why... However the main problem is that each query is way to slowly and I cannot figure out any other better method to retrieve this result... I am missing anything here? Is there any other better method to return the number of results with a certain condition for a large database? Any help would be really appreciated Regards, Arthur
