Arthur, What about
select count(category) use index(category) from books where category=1 (don't think this will be faster, but try it) Then you could try to run "OPTIMIZE TABLE books" (read the manual first if it's a live system!!!!) Andy > -----Original Message----- > From: Arthur Radulescu [mailto:[EMAIL PROTECTED] > Sent: 07 September 2004 15:03 > To: Andy Eastham; Mysql List > Subject: Re: problems counting the number of returned rows > > 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]
