Thanks for the tip! It is much faster now... But it still takes about 3 seconds which makes about the same thing like using count() so this still does not solves the problem
Regards, Arthur > Arthur, > > Is it faster if you do: > select SQL_CALC_FOUND_ROWS category use index(category) from books > where category=1 limit 0,10 > > ie change "*" to "category" (which can be read from the index)? > > Andy > > > -----Original Message----- > > From: Arthur Radulescu [mailto:[EMAIL PROTECTED] > > Sent: 07 September 2004 14:23 > > To: [EMAIL PROTECTED] > > Subject: problems counting the number of returned rows > > > > 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
