Result Location

Shawazi

If I select a single row from a table and order results by the primary key (auto incrementing #), how would I know the position my selected result is in? So "Result 5 of 58" works without running loops.

Shawazi

I just figured I can do

mysql_query("SELECT (COUNT(id)+1) AS curPosition FROM table WHERE id < $current_id");

Is this the best way?

liquorvicar

If your primary key is auto-incrementing, can't you just use that?

Shawazi

No because there is actually another field used for WHERE so it is possible for the numbers needed to be 3 5 6 because 4 belongs to another set.

liquorvicar

Can you explain exactly what you're trying to achieve (give examples if necessary)? Your original post said you were selecting one row from the table.

Shawazi

Each row in the table has an image location, an id, and what group it belongs to. I am using the above line to determine what position the image is in, within the possible result set. So image 5 of 10, where 5 was from this query. The actual query has an addition like:

WHERE id < $current_id AND group_id= $group_id

liquorvicar

I'm with you now. Yes, your solution would work, or else a bit simpler

SELECT COUNT(*) AS curPosition WHERE id <= $current_id AND group_id= $group_id

Shawazi

You need +1 or if you are the first position, the amount will be zero, and so forth meaning everything is always one less. Also using * is a waste since you are counting everything instead of just the id right

liquorvicar

No, you don't need the +1. Notice I changed the less than (<) to less than or equals to (<=). It makes more sense that way and will be more obvious if you come back to it in several months time.

As for the count(), I don't believe it makes any difference. count() will just count the rows, count(id) will count the id's. Both numbers will be identical.

Shawazi

Oops I missed the <= change. But you definitely want to use count(id) because count(*) counts how many rows there are after getting all the fields instead of just getting the id

liquorvicar

It may be quicker to use count(id), I'm not sure. If it is quicker, it's not for the reason you give as the query is not returning the data, it's just counting the rows.

From the MySql Docs:

COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause.

Given that you have a WHERE clause your method may be quicker.

Shawazi

interesting. thanks.

liquorvicar

Just did a quick benchmark. Ran two queries against a table with 28 columns and 18,000 rows with a WHERE clause. Ran each one 1000 times and the count(*) version was about 3 times as fast as the count(id) version (ran the whole test twice with the same results both times)!