2

I have a .sqlite db which contains only one table. That table contains three columns and I am interested in indexing one column ONLY.

The problem is, when I perform the indexing, I got an empty index table !

I am using SQLite Manager add-ons for Firefox. This is the syntax that appears before I confirm the indexing:

CREATE  INDEX "main"."tableIndex" ON "table" ("column1" ASC)

I don't know what is the problem here. I tried this tool - long time ago - with another database and it works fine.

Any suggestion ?

iTurki
  • 16,292
  • 20
  • 87
  • 132

2 Answers2

3

You cannot "see" the contents of a database index. No table or table-like structure is created that corresponds to the index. So there is nothing to look at that could be empty.

If the CREATE INDEX command ran without error, you can be confident that the index was created and will continue to be maintained by SQLite as you add, remove, and update data.

As per the comments, below, @iturki is actually trying to index for full text search. SQLite supports several extensions for full text search but they are not implemented through the stanard CREATE INDEX command. See this reference.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • 1
    What makes me confused is the size of the file before and after the indexing. It is the same! No change AT ALL. – iTurki Nov 15 '11 at 20:28
  • Is there any data in `table` when you create the index? And, did you make sure to `commit` the CREATE INDEX statement before checking the database size? – Larry Lustig Nov 15 '11 at 20:29
  • Yes, there are more than 300 records. And yes, I committed the statement. – iTurki Nov 15 '11 at 20:34
  • Hmmm. What column exactly are you indexing? Is it one that was already indexed as a primary or foreign key? After you issue the command, if you do `.schema` for that table, is the index listed? – Larry Lustig Nov 15 '11 at 20:35
  • With only 300 records, chances are you had space on the last allocated page in the file for the index. – Larry Lustig Nov 15 '11 at 20:36
  • I want to index one column only. It is `longtext`. It is neither a primary nor a foreign key. And I don't know what do you mean by doing `.schema` for the table. – iTurki Nov 15 '11 at 20:45
  • To be in the same page, what I'm trying to do is `full-text` indexing. I'm doing that to be able to search in that table. – iTurki Nov 15 '11 at 20:46
  • SQLite supports full-text indexing, but not using CREATE INDEX. See here: http://www.sqlite.org/fts3.html. – Larry Lustig Nov 15 '11 at 20:49
  • Really helpful link. Please update your answer with your last comment. I'll accept it right after I figure out this `fts` thing and resolve my problem. Thanks man :) – iTurki Nov 15 '11 at 20:56
1

Try use VACUUM query. It will completely rebuild sqlite database file and will rebuild all indices and reset all ROWID etc.

Serhii Mamontov
  • 4,942
  • 22
  • 26