Hi!
>>>>> "Emmanuel" == Emmanuel van der Meulen <[EMAIL PROTECTED]> writes: <cut> >> This will of course slow down any query significantly, if the WHERE >> clause matches a lot of rows, but should still be faster than: >> >> - retrieving all rows to get a count. >> or >> - Do two queries: >> SELECT COUNT(*) FROM ... >> SELECT .... LIMIT X Emmanuel> Monty, when I do two queries; Emmanuel> 1. select * from memberships,membershipstracking where Emmanuel> memberships.email=membershipstracking.email order by Emmanuel> membershipstracking.activitytimestamp desc limit 50 Emmanuel> 2. SELECT COUNT(*) FROM MEMBERSHIPS Emmanuel> both queries run faster than with SQL_CALC_FOUND_ROWS, 1 runs in 3-5 seconds Emmanuel> and 2 in 4-5 seconds, thus totalling 7-10 seconds. The reason the COUNT(*) is faster is that in the case of SQL_CALC_FOUND_ROWS MySQL needs to do sort and retrieve all matching rows, while COUNT(*) can skip the sorting phase. This is one of the boarder cases where SQL_CALC_FOUND_ROWS can be slower than using 2 queries. Note however that if there would have been less matching rows (for example 60), the SQL_CALC_FOUND_ROWS method is much faster... <cut> >> By the way, why do you need to know the number of rows ? >> >> If this is for a web form where you want to show X rows, a better way >> to do this is to retrieve X+1 rows and then, after displaying X rows >> say (if you get X+1 rows) that there is more matching rows in the >> result. Emmanuel> On an administration web page I show total number of members and then the Emmanuel> most recent joinees. The question here is how important it is for anyone to know the total number of members. In many cases it's enough to show X members and then have a link 'show the next X members', that is only shown if there is more than X members. The speedup you get for doing this is in many case worth the small inconvenience for your users.. Regards, Monty --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
