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]