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]
