SQLite Index

[jcpetu]

Hi,

I would like to know if it is any reference on how can I use the INDEX feature of SQLite whiting Autoit.

thanks in advance and regards.

[Zedna]

http://www.sqlite.org/lang_createindex.html

Try to search this forum for:

+sqlite +create +index

[jcpetu]

Hi Zedna, I can create the Index with sqlitebrowser.exe no problem, thing is how can I use it in Autoit.

[Zedna]

_SQLite_Exec($hDB, "create index indexname on table (column1,column2);")

Edited October 12, 2014 by Zedna

[Zedna]

According to using existing index look here

http://www.sqlite.org/lang_select.html

http://www.sqlite.org/syntaxdiagrams.html#table-or-subquery

at "table-or-subquery" section

it seems that syntax is as follows

select * from table indexed by indexname where ...

Edited October 12, 2014 by Zedna

[jcpetu]

and to use the index to search for a record?, how can I open de base indexed and then search for a record?

 

With no indexed database I do:

_SQLite_QuerySingleRow($dbn, "SELECT * FROM Contacts WHERE name='" & $s1 & "' ORDER BY name", $r) == $SQLITE_OK

[jcpetu]

Zedna, I just saw your answer. I'll give it a try.

thanks a lot and regards.

[Zedna]

As far as I know SQLite use (in SELECT,UPDATE,DELETE) apropriate index automatically.

You have to just create suitable index.

But as I have written, you can explicitly use particular index in SELECT clausule, see my previous post ...

Edited October 12, 2014 by Zedna

[jcpetu]

Thanks a lot.

[Zedna]

As far as I know SQLite use (in SELECT,UPDATE,DELETE) apropriate index automatically.

 

You can check it yourself, just measure time for desired SELECT FROM WHERE ... with and without index.

You will see that you don't need to explicitly write index in SELECT and SQLite will use it anyway if it's available.

Without index it will be slower.

Edited October 12, 2014 by Zedna

[jchd]

Post your database schema and typical queries so we can advise you efficiently.

Beware of using = as this isn't case sensitive while LIKE is by default (albeit slower).

Also whenever $S1 contains a single quote, like "O'Connor" then your query will error out. The correct way is:

_SQLite_QuerySingleRow($dbn, "SELECT * FROM Contacts WHERE name='" & _SQLite_FastEscape($s1) & "' ORDER BY name", $r) == $SQLITE_OK