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