|
Sean Davis
sdavis2 at mail.nih.gov
Thu Oct 5 06:38:59 EDT 2006
On Thursday 05 October 2006 06:00, durumdara wrote: > Hi ! > > I wrote some mails about this theme, but I forgot to ask an important > question: > How to I get the number of elements in the query... ? > > These valued needed for the Pager: > > 1. "Split by" - the max. record number you can see in the page. > 2. "RecordCount" - how many records you have (total) - to calc number > of pages. > 3. "ThumbCount" - how many page thumb (link) you see in one page. > 4. "Actual Page Number" - where are we in the page collection. > > Simply I get every of these values except RecordCount. To get it, I must > count the records in the full query... > I have many elements (1000, 10000 or 100000), so I don't want to fetch > them all. > Because this I need to exec the query in twice, because I need to get > the count of elements, and I need some elements from it. > > 1.) select count(*) from ... > 2.) select * from ... > > Some of the databases supports "repeatable read" transaction isolation, > and they are protect the view to I get same thing in different times. > But if database not supports this, I can get different values with these > queries - example: new record inserted after COUNT(*), so number of the > elements is greater than in the first query... > > How do you handle this problem in MySQL, or other database that cannot > isolate the transactions (example) ? I don't think that you can without locking the table, which you probably don't want to do for performance reasons. Making you pager robust to slight variations in the total number of rows will be important in this case. Sean
|