I'm still trying to come up with an efficient way to query my table of names for all
first initials. Seems to have stumped everyone.
I.e. There are 50,000 names, and I want the final result to be:
A, B, C, F, H, I, J, K...........
That is, a list of all first initials that are actually present in the data (and
ideally are also used in a joined table).
I haven't been able to think of a way to do this efficiently. My current query looks
like this:
select DISTINCT UPPER(LEFT(n.Name,1)) as Initial
from Names n, Things t
where n.ID = t.ID
order by Initial desc
Even if I eliminate DISTINCT, or create a single character index on Name, or create a
whole field that just has the first character of Name, I can't figure out how to get
MySQL to not have to scan the entire table. I get an EXPLAIN that looks like this:
+-------+------+-------------------+-----------+---------+-------------+-------+-----------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+-------------------+-----------+---------+-------------+-------+-----------------------------------+
| n | ALL | PRIMARY,ID | NULL | NULL | NULL | 57674 | Using
temporary; Using filesort |
| t | ref | ID | ID | 5 | n.ID | 4 | where used; Using index;
Distinct |
+-------+------+-------------------+-----------+---------+-------------+-------+-----------------------------------+
Is there any way to do this, or an efficient way to query the table 26+ times with a
list of first initials?
(My actual query examines 166,000 rows and takes 12 seconds to run, all to give me a
list of most of the alphabet!)
Thanks in advance,
TK
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
