« Chasing Lance | Main | Virtual Bookshelves »

January 12, 2006

MySQL LIMIT Secrets

I've been using Mysql for about 5 years now and I like to think I'm an expert (in my own mind at least), but I know my way around an outer join and I can get MySQL to do pretty much whatever I wanted to do. For quite some time, when I needed to do paging of web results that spanned more than one page, I would keep track of the current page, the number of items that I needed to display, and the band of data that I needed to show to the user. This required me to do a bunch of data pulls, counts, exclude data, keep special indexes... you get the point. So I needed to do a paging function recently and I decided to google it.

I found a few intersting entries that were related to my search, but the one that was really interesting came from the OReily site. They had a chapter online for the MySQL Cookbook. The chapter (3 I think, but I'm not sure) covered select statements, but they also covered the LIMIT operator. It turns out that the LIMIT operator will do exactly what I used to do by hand for all these years in a single operation. There is a 2 arguement version of the operator that skips N records and then limits the data to M items so LIMIT 10,20 skips the first 10 items from the query and pulls the next 20. This makes paging so simple that I was able to add a function to my new site in about 10 minutes. So now I need to do 2 things. Spend more time reading my manuals, and buy the MySQL Cookbook.

Posted by Hefe at January 12, 2006 06:44 AM

Comments

Thanks for this short tutorial...I put it to use for making the pages of a gallery of Obama t-shirts.

Posted by: Ben S. at June 8, 2008 04:52 PM

Post a comment




Remember Me?