In article <4.01956188678741.333.320654392242@stripped>,
TK <kmysql@stripped> writes:
> 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.
Other DBMSs like PostgreSQL grok indexes on functional expressions;
MySQL doesn't. Thus your only choice seems to be storing the
uppercased initial in a separate column and putting an index on that
column.
