List:General Discussion« Previous MessageNext Message »
From:Adam Wolff Date:April 20 2006 9:00pm
Subject:finding a record within a sort order
View as plain text  
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 | Price_Eldridge@stripped      |
+--------+----------------+-------------------------------+

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
Thread
finding a record within a sort orderAdam Wolff20 Apr
  • RE: finding a record within a sort orderNicolas Verhaeghe21 Apr