So about a year ago I was working on a client project and stumbled upon the SQL_CALC_FOUND_ROWS and FOUND_ROWS commands which you can use in SQL queries that have LIMIT statements to return the entire amount of rows that would be returned if the LIMIT had not been used.
This was very benficial since the paginator needs both of these values. We only wanted to return the rows that are part of the page yet we still want to return the total number of rows returned. Because of this "trick" we were able to shave seconds off the queries done by the paginator (which in computing terms is a lot of time). I thought this was very cool.
If you are wondering, here is what the query looks like:
select SQL_CALC_FOUND_ROWS from tableName limit 1;
select FOUND_ROWS();
The first query will only return 1 row. The second query will return the number of rows that would have been returned if the limit were not in place in the previous query.
Now I've found something that's even cooler. First (many of you may already know about this), I found that you can actually do select statements inside your select expressions:
SELECT
(select count(*) from users) as 'UserCount'
username
FROM
users
This would return the total number of users along with the username for each row that is returned. Granted, that isn't very useful which is when I stumbled into the coolest thing I think I've seen yet (maybe it's cool because it's new, who knows =)). Take a look at this:
SELECT
(select @row := @row+1) as index,
username
FROM
users,
(select @row:=0) rowcount
In a single query, I am able to set a variable (@row), and increment it for each row. It looks like it won't work, but it does. This allows me to have an auto_incrementer without having to actually have and maintain a column in the database. Not to mention, I can sort and order the rows and redisplay the index however I want.
That to me is cool!
