List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 29 1999 5:54pm
Subject:No Index on STRCMP(), why?
View as plain text  
>>>>> "jordanh" == jordanh  <jordanh@stripped> writes:

jordanh> 	I am sure there is a perfectly reasonable answer for this,
jordanh> however, I would like to know what this answer is:

jordanh> 	Why can't mysql use an index for a column when a strcmp is
jordanh> performed?

jordanh> 	We have a medium sized database that _must_ (on one table anyway)
jordanh> be keyed on a case-sensitive name.  This means that DELETEs, UPDATES,
jordanh> etc. to a row are getting slower and slower with every row added to the
jordanh> DB.

jordanh> Could an index device be invented to at least eliminate the number of rows
jordanh> that a strcmp must be evaulated against?

jordanh> 	I do know that I could optimize my application further by caching
jordanh> the unique interger key from the table, but I thought I would still ask...


Why do you need to use strcmp() ?

Just declare your columns binary if you don't want it to be compared
case sensitive.

Another option is to use the query:

SELECT * from table where column=X and strcmp(column,X)=0

The above query will use keys!


No Index on STRCMP(), why?jordanh29 Jun
  • No Index on STRCMP(), why?Michael Widenius29 Jun
    • Re: No Index on STRCMP(), why?Jordan Husney29 Jun