At 05:02 PM 6/12/2004 +0200, Harald Fuchs wrote:
>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.
As I indicated, I already tried that it had no effect at all. MySQL still scans the whole
table, still doesn't use any indexes, and takes forever at it.
Thanks,
TK
>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.
