[ale] MySQL question on indexes

[Gary S MacKay]

I agree, I'll probably have to rework the use of indexes in the new app. As 
a note tho, the use of uppercase() in an index does not index only those 
columns which contain uppercase letters, it creates an index on a field as 
if the field contained all uppercase. I'll try to explain. The field may 
actually contain 'mylastname' but if you could 'look inside' the index, it 
would contain 'MYLASTNAME'. So a "SELECT * FROM mytable WHERE lname = 
'MYLASTNAME'" would find that record. If it was just indexed normally, it 
would not find it since 'mylastname' does not equal 'MYLASTNAME'. Clear as 
mud? ;)

Thanks for your help,
- Gary

At 09:42 AM 3/5/2002 -0500, Jim Philips wrote:
>I don't see a way to create an equivalent index in MySQL. Probably it's
>best to work backward and understand the purpose of the index. As I
>understand it, you are trying to index only those columns that contain
>upper case letters? What is the query that benefits from this index?
>There may be other ways to oprimize it that buy you just as much
>efficiency. You could do an explain on the query within MySQL and begin
>to get an idea. But nothing I see in the MySQL docs indicates that you
>can limit indexing with a function for upper or lower case.
>
>
>On Tue, 2002-03-05 at 08:09, Gary S MacKay wrote:
> > Exactly my problem. I'm converting an old DOS Foxpro app with standard
> > dbase tables to a Windows app using MySQL as the backend. Foxpro allows
> > using functions in an index, ie "index on upper(lastname) tag lastname".
> > I've already written a routine in Foxpro to create the tables in MySQL and
> > copy the data across. Works fine. The last step is getting the indexes
> > across, and as long as the index is simply the field name, it works fine.
> > Where I run into problems are the indexes that use functions like
> > 'uppercase', or 'lowercase', etc. etc. as part of the index expression.
> >
> > - Gary
> >
> >
> > At 07:59 AM 3/5/2002 -0500, Jim Philips wrote:
> > >I don't understand. Do you want to index a table in the database? What
> > >kind of function is this? Neither is a listed function for PHP.
> > >
> > >
> > >On Mon, 2002-03-04 at 23:07, Gary MacKay wrote:
> > > > Is it possible to create an index using a function, like upper() or
> > > > ucase() or whatever?
> > > >
> > > > - Gary
> > > >
> > > > ---
> > > > This message has been sent through the ALE general discussion list.
> > > > See http://www.ale.org/mailing-lists.shtml for more info. Problems
> > > should be
> > > > sent to listmaster at ale dot org.
> > > >
> > >
> > >
> > >
> > >---
> > >This message has been sent through the ALE general discussion list.
> > >See http://www.ale.org/mailing-lists.shtml for more info. Problems 
> should be
> > >sent to listmaster at ale dot org.
> >
> > Edison Information Technologies                 www.EdisonInfo.com
> > P.O. Box 554                                    Gary at EdisonInfo.com
> > Milan, OH 44846-0554                               419.499.7040
> >
> >
> >
> > ---
> > This message has been sent through the ALE general discussion list.
> > See http://www.ale.org/mailing-lists.shtml for more info. Problems 
> should be
> > sent to listmaster at ale dot org.
> >
>
>
>
>---
>This message has been sent through the ALE general discussion list.
>See http://www.ale.org/mailing-lists.shtml for more info. Problems should be
>sent to listmaster at ale dot org.

Edison Information Technologies                 www.EdisonInfo.com
P.O. Box 554                                    Gary at EdisonInfo.com
Milan, OH 44846-0554                               419.499.7040



---
This message has been sent through the ALE general discussion list.
See http://www.ale.org/mailing-lists.shtml for more info. Problems should be 
sent to listmaster at ale dot org.