title: MySQL/Classic ASP - Using LIMIT for paging
body: <p>I have encountered a problem with my build.</p>
  
  <p>I was using a very old method for recordset paging, that did not use the method, LIMIT. I have now used the LIMIT method, which has speeded up my page immensely... but it has affected another part of my page and I'm too sure how I can get around it.</p>
  
  <p>Before, I would run a query like this (example only):</p>
  
  <pre><code>SELECT photoID FROM photoSearch WHERE country = 'London'
  </code></pre>
  
  <p>Even though this query would now return 69,000+ photos from London, I would then write <em>all</em> theses results (photoID INT INDEX, userID INT INDEX) to a table (searchResults), so the results could be used in other parts of the page. So I have this:</p>
  
  <pre><code>While NOT rs.EOF
  INSERT INTO searchResults (photoID, userID) VALUES ("&amp;photoID&amp;","&amp;userID&amp;")
  rs.MoveNext
  Wend
  </code></pre>
  
  <p>Okay, now further in to my page, I want to show a list of the people in the photographs, along with the count, that are in <em>all</em> those results just stored. This enables my user to narrow the results to something more specific. For example, somebody could search for "Golden Globe Awards", the photos return and they can see a list of people that were at the Golden Globes, such as, Brad pitt (8,374), Angelina Jolie (7,321) etc etc. So I have this query which does just that:</p>
  
  <pre><code>SELECT COUNT(*) AS totalPeople, people FROM people
  INNER JOIN photopeople ON photoPeople.peopleID = people.PeopleID
  INNER JOIN searchPhotoResults ON photopeople.photoid  = searchPhotoResults.photoID
  GROUP BY people
  ORDER BY totalPeople DESC
  LIMIT 5
  </code></pre>
  
  <p>But now, because my DB has grown over the last year, my search for "London" could return 69,000 rows, so it takes a little too long to search the rows and write the rows to my results table. So I was advised to use LIMIT paging, where instead of returning 69,000 rows, it would only return the amount required for that page, like 20.</p>
  
  <p>I have now changed my page to use LIMIT, but as you can probably work out, I've lost the functionality of my list of peoples names that attended that event. Instead of Brad Pitt (8,374), it is now Brad Pitt (20) and Angelina Jolie (20)... which is just no good.</p>
  
  <p>So this is my dilemma, I want (need) to use LIMIT to speed up my page but it takes away the list of peoples names, which I also really want. If I do not use LIMIT, my search is too slow on the more common words - it seems I can't win!</p>
  
  <p>Is there anybody that could advise me on how to get around this? I hope I explained it well...</p>