0

I needed to manually create an index for my table because rails/sqlite3 because collation hasn't been supported yet.

So in my migration I have:

## This doesn't work, so we'll do it manually
#add_index :events, :name, COLLATE: :NOCASE
execute <<-SQL
  CREATE INDEX 'index_events_on_name' ON 'events' ('name' COLLATE NOCASE);
SQL

But evidently the index isn't being used, as I can see by trying to search for events using a name in the table, but with different case.

How can I get sqlite3 and rails to use the indexes I've created?

As an example, if I have an event with the name "Fred" and I search with:

Event.where("name == ?",nameVar)

Then I can find it when nameVar=="Fred" but not when nameVar=="fred"

Community
  • 1
  • 1
David Ljung Madison Stellar
  • 3,008
  • 1
  • 26
  • 46
  • 1
    Show the code that tries to use the index. – CL. Jun 21 '16 at 07:48
  • Perhaps that is the problem - the examples I've seen have shown that making an index on 'name' means that searches on 'name' will use that index. Am I supposed to supply the index to the search somehow? Perhaps the question could be rewritten as "how do I use an index to do a search in rails?" – David Ljung Madison Stellar Jun 22 '16 at 22:00

1 Answers1

1

A COLLATE NOCASE index is used only if the search also uses this collation.

This happens when

  1. the column itself is declared this way:

    CREATE TABLE events( ... name TEXT COLLATE NOCASE ...);

  2. or the search explicitly uses a case-insensitive comparison:

    SELECT ... WHERE name COLLATE NOCASE = ?;

  3. or you are using LIKE, which implicitly is case insensitive:

    SELECT ... WHERE name LIKE ?;

    (Look out for pattern characters. And an index for LIKE also requires the column to have text affinity.)

CL.
  • 173,858
  • 17
  • 217
  • 259
  • You said "2. or the search..." Shouldn't that be "and the search"? I.e., I have to create the table with the index and then use collate nocase in the search? I know that 2 and 3 are possible without using a pre-created index, so I'm trying to figure out how to make sure it's using the index I've created to get the speed savings, though I suppose I could start doing some benchmarking... – David Ljung Madison Stellar Jun 23 '16 at 08:38
  • Indexes and comparisons use the column's collation by default. – CL. Jun 23 '16 at 09:27
  • So then creating the index with the collation specified as nocase means that it should be using that when I search that column, no? Because it doesn't seem to work. – David Ljung Madison Stellar Jun 23 '16 at 10:04
  • If the index uses the column's collation, but the search uses a different collation, then they do not match. – CL. Jun 23 '16 at 10:26
  • Unfortunately #2 doesn't work on my sqlite3 without #1 as well. So maybe this is a sqlite3 question and has nothing to do with rails... but as I understand things, you can create a nocase index on a column that isn't necessarily set as nocase. Is that not true? – David Ljung Madison Stellar Jun 27 '16 at 09:16
  • Okay - I think it makes sense now, turns out the collation of the index doesn't matter, it's the table creation that determines collation. If you update your answer, I'll choose it! – David Ljung Madison Stellar Jun 27 '16 at 09:52
  • #2 is tested. All three require that the index is COLLATE NOCASE, too, but this happens automatically only for #1. – CL. Jun 27 '16 at 10:52
  • (Sorry that took so long to achieve understanding... :) ) – David Ljung Madison Stellar Jun 28 '16 at 20:35