How do I find out the index in a MySQL table

Question

Hey guys, I'm programming in PHP5 & MySQL5 and I'd like to know how to figure out the index of an entry. To be clear, here's an example: I have 500 users in a table, I want to select the user johndoe and the index of that user, so if johndoe is the 100th user in my table, I want to be able to select the information for him in a similar way to this:

SELECT *, INDEX_OF(id) FROM users WHERE username='johndoe';

So I'd get the details about the user back (name, username, id, index etc...) Please note this is different than the ID of the user.

How would I go about doing this?

Thank you!

Answer 1

You can use the _rowid special column, but it is just the same as an "unsigned int auto_increment primary key", i.e. what you would normally call "id".

SQL has no requirements on order of the rows searched, only on returned rows. That means you can't count on that entry being at the 100th position for every search. So in that sense, any attempt to find the physical position of the row, could possibly return a different result each time.