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 <[EMAIL PROTECTED]>, >TK <[EMAIL PROTECTED]> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
