Total Rows In MySQL Query ‘Trick’

Background: I wrote this post last week, after discovering what I thought was a nifty trick. Shortly afterwards, I found contradicting evidence that suggested the traditional route of using a separate query of COUNT(*) was faster, and/or it depends on your individual database design. Instead of deleting the post, I figured I should publish it anyway, along with a link to the article that I read, so that those who give a crap about this sort of thing can make their own mind up.


One of the benefits of working on and releasing rev.iew.me — apart from giving me an active place to review (I typed rev.iew first, damnit) — was the excuse it gave me to learn more stuff. I like learning stuff.

Here’s a nifty little MySQL ‘trick’ that I discovered, that helps cut down on unnecessary bloat. When doing pagination, I’d always create the query to deliver the results and a second query to COUNT() all rows, and this adds extra load time. Using SQL_CALC_FOUND_ROWS and FOUND_ROWS(), we still have two queries but the second is faster because because the result set need not be sent to the client (source)

Pop SQL_CALC_FOUND_ROWS in to your query just after the SELECT, and do another query directly afterwards using SELECT FOUND_ROWS(); E.g.

$query = mysql_query("SELECT SQL_CALC_FOUND_ROWS * FROM `members` LIMIT 0, 15");
$total = mysql_query("SELECT FOUND_ROWS();");

Assuming there’s 50 members in the members table, we now have the result set for 15 members in $query and the total row count in $total (use mysql_result() to access the number directly). Nifty, huh?


PHP Mail Form Update

I’ve just released version 2 of my free PHP mail form and heartily welcome feedback from those currently using version 1 of the script. (That means: someone hurry up and test it so I don’t have to! Kidding, kidding…)

Is Your Pipe Wrong?

Something that came up in conversation this weekend (well, this is what happens when you get more than one geek in a room)… on many keyboards, the pipe is in the wrong place. The pipe — that’s | to you non-geeks — is just to the right of the left-hand shift key on UK keyboards…. read full entry »


Super Productive

I’ve been super productive, and answered all of my questions! No, not really, Rachael did it. Teehee.


Furry Friday: Innocent

I wanted to give you all piggie pics today, but they’ve not sat still for long enough and the light has been terrible, resulting in absolutely zero usable pictures. Anyway, settle for catties… (well, one catty): Fudge has such an innocent face, until you catch him stealing your chicken out from under the grill (for… read full entry »


Twitterytwitter

After spending the past couple of years or whatever blasting the shit out of twitter because it’s encourages inane BS, I actually got one. To reserve my username, of course, no other reason :P

Animal Crossing: Lets Go to the City Review

I can’t remember when I first got hooked on Animal Crossing… it must have been not long after the DS version came out (“Animal Crossing: Wild World“) because I’d never heard of it back when I spent weekends hard-wired in to the GameCube. I know that I’d read online that it was similar to Harvest… read full entry »


Dying Girl is Not Dying

I don’t know of how many of you will recall this, but when I gave out my last Pants Award, the recipient turned around and told me that I shouldn’t have criticised her site because she has terminal cancer. I’m not sure in what way the two are related — cancer doesn’t cause you to… read full entry »

I’m a Whiny Cow

I’ve just sent in a complaint email to the Mars company/customer care people, because every Malteser I’ve had so far from a bag I bought today has tasted like plastic. I just wanted some tasty, crispy chocolate, jeez. :(


Live.com Fake SERP Referrals Are Pissing Me Off

Melissa and I had a short chat about Live.com search results in our stats a while ago. Long story short, a whole bucketload of search result click-throughs were showing up in our hits that were either a) irrelevant to our sites or b) were bullshit, because we weren’t ranked for that term anyway. This wasn’t… read full entry »

Christmas Wishlist

Because I’m a materialistic twat, and know full well that at least two people buying me gifts this year read my website, I’ve compiled a short list of things that I’d like for Christmas. :D An Asus EEE netbook. Linux version, preferably the 901. (hahaha, keep wishing Jem) High Performance MySQL by multiple authors The… read full entry »

Rev.iew.me Christmas Contest

I’ve teamed up with Jordan at Holdfire hosting to offer 6 months hosting and a domain absolutely free in the rev.iew.me Christmas contest — review and win :)