Simon, Thanks for the detailed response!
Can I say: Create INDEX c on myTable(c); Vance on Mar 07, 2013, Simon Slavin <slav...@bigfraud.org> wrote: > > >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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
