Re: [GENERAL] Yet Another (Simple) Case of Index not used

Stephan, Martijn,
good call, that was it: the C locale.

I had used all the default settings when installing/creating the database,
and apparently it used my default locale (en_US).
I recreated (initdb) the database with --no-locale, and recreated the database,
and sure enough, the query:
select count(*) from table where table.column like 'fol%'
was a zillion (well almost) time faster than it used to be,
and on pair with mysql's performance.
And as expected, the EXPLAIN on that query does show indeed
the use of the index I had created on the table.

Sweet, I can now nuke mysql out of my system.

Folks, thank you all for the help and other suggestions.

Denis Amselem
Next2Me Inc.




Stephan said:
  > If it doesn't use the index (ie, it's still using a sequential scan)
  > after the enable_seqscan=off it's likely that you didn't initdb in "C"
  > locale in which case like won't use indexes currently (you can see the
  > archives for long description, but the short one is that some of the
  > locale rules can cause problems with using the index).

Martijn said:

  > Ah, but that may be caused by something else altogether. LIKE is only
  > indexable in the C locale so if you have en_US as your locale, your LIKE
  > won't be indexable. See the discussion threads on this mailing list in the past.
  >
  >