mysql get real row number

i have this table

mysql> select name from test;
+---开发者_JAVA技巧------+
| name    |
+---------+
| foo     |
| bar     |
| foobar  |
| anton   |
| budi    |
| anton S |
+---------+
6 rows in set (0.00 sec)

how to know if first 'anton' is record number 4?

is there any faster query than this...

select rank from (select @rownum:=@rownum+1 rank,p.name from test p, (SELECT @rownum:=0) r) a, (select * from test where name like 'anton%' limit 1) b where b.NAME = a.name

No.

There is no such thing as a "row number" in a table; only in an ordering imbued by ORDER BY (or possibly GROUP BY).

Records in tables have no inherent ordering, even though without an ORDER BY clause you may often happen to see them presented in insertion order for implementation reasons.

If you want to make use of insertion order, add an auto-incrementing ID column and use that.

The only "fast" way to have a "row number" is to add a "row counter" column :) The problem there is that you have to keep your records up to date when you delete some of them. If there is no DELETE action (or they are very limited) , a column would be an acceptable choice.