Hello Monty, With your help I'm getting the understanding I was looking for, thank you.
On 27 January 2002 23:23, Michael Widenius wrote; > 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. Makes sense. Slowly but sure I'm beginning to see the bigger picture. > 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... Seeing as the sort would only be of 60 records. > <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. Not critical. So on two queries where I use this, I now know why they are slightly slower than expected. That is if I continue to do 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.. Point taken and a neat way to overcome the delay for getting the total count. BTW, in this case I do not show all the detail, only the most recent members that joined. Kind regards Emmanuel > 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
