On 7 Mar 2013, at 2:51pm, ven...@intouchmi.com wrote: > I haven't dealt with indexes in SQLite in the past, so a couple of follow up > questions: > To index a column (a non primary key), I assume I have to create another > column using the Create index with a new column name.
There is no need to create a new column. You can have a table which has existed for years with columns a, b, c, d. Years after that table was originally created you can create a new index on existing columns of it: CREATE INDEX myTable_b_c ON myTable (b, c) The table is untouched and has exactly the same content as before. SQL just has a new faster way of searching it which may be useful for some statements. There are no additional columns anywhere. This is true for all SQL engines, and it not an answer especially about SQLite. > When a query is made to the table do I need to reference the index column > name or can I reference the original column and still invoke the index? It is SQL's job to know what indexes are available and to guess the fastest way to do whatever command or query you are executing. If a programmer ever needs to specify which index SQL should use to do something, something is wrong. The intelligence it takes to have SQL figure out which indexes to use and how to use them, without any input from the programmer, is one of the things that makes SQL so good. > In MySQL it seems that I can simple declare a column as an index and and use > that column's name in querys. You may be confused. In SQL you can use any column name in queries whether it is indexed or not. Having a convenient index may make a command faster, but it doesn't magically let you do anything that wasn't possible without it [1]. Again, this is true of all SQL engines, not just SQLite. > No need to worry about an additional column name. SQLite needs no additional columns. I'm not sure where you have read about additional columns being needed for any SQL engine, but the idea is not one that SQL users use. You may have read some very weird books or tutorials, or you may be trying to use some knowledge you have about some other DBMS when working with SQL, where it doesn't apply. [1] certain exceptions for some obscure advanced features (e.g. foreign keys in SQLite) but you don't care about that Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
