Already tried it.. It is just as faster as the others... And I have already optimized the table... The server is a dual processor with 2 GB ram so this should be not a problem at all... This table has about 7 fields... The main issue is that it has more than 3 millions records and here is where the problem comes from...
Thanks a lot, Arthur > 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] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
