MySQL does seem to have a row number function and the purpose of this is because it increases the processing time, as you have seen with your workaround.
I would instead create a temp table with an autoincremented ID, insert the content of your record set into it and use this autoincremented ID as a row number. Of course the temp table is to be dropped or your result will be polluted with the result of previous requests. Or I would do it program-side, for instance if you use a PHP Loop. -----Original Message----- From: Adam Wolff [mailto:[EMAIL PROTECTED] Sent: Thursday, April 20, 2006 2:01 PM To: mysql@lists.mysql.com Subject: finding a record within a sort order I have a table with a large number of rows. I have the primary key for a record within the table. The record I'm looking for looks like this: +--------+----------------+-------------------------------+ | id | fullname | email | +--------+----------------+-------------------------------+ | 123456 | Eldridge Price | [EMAIL PROTECTED] | +--------+----------------+-------------------------------+ The only way I've found to find the ordinal number of a record within a sort is to use the count(*) subquery trick, like this: SELECT contacts.*, (SELECT COUNT(*) FROM contacts AS c2 WHERE contacts.fullname > c2.fullname OR (contacts.fullname = c2.fullname AND contacts.id > c2.id) ORDER BY fullname) AS ord FROM contacts WHERE contacts.id=123456; (I added the second condition to the WHERE subquery clause because there isn't a guarantee that fullname is unique.) This takes a substantial amount of time to run, even though I have indices on every column. EXPLAIN says: +----+--------------------+----------+-------+------------------+----------+ ---------+-------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+----------+-------+------------------+----------+ ---------+-------+--------+--------------------------+ | 1 | PRIMARY | contacts | const | PRIMARY | PRIMARY | 4 | const | 1 | | | 2 | DEPENDENT SUBQUERY | c2 | index | PRIMARY,fullname | fullname | 101 | NULL | 195664 | Using where; Using index | +----+--------------------+----------+-------+------------------+----------+ ---------+-------+--------+--------------------------+ Although I must admit that I really don't know what this means. It seems like both const and index type queries should be fine. I also have a follow up: assuming that the enlightened folks on this list can help me optimize the above, I'm looking for a way to pass this information into an OFFSET value, as my end goal is retrieve a page from the table in the given sort order starting with the record whose id I have -- with a single SQL statement. Obviously, this isn't legal syntax: SELECT * FROM CONTACTS ORDER BY fullname LIMIT 10 OFFSET `SELECT contacts.*, (SELECT COUNT(*) FROM contacts AS c2 ... but that's conceptually what I'm looking for. Thanks! Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
