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
