Upgrade to Lion -- why Valentina has columnar format ...

[Peter Haworth]

SQL only creates indexes on the fly in very specific circumstances so most
of the time, you'll get a serial scan if your'e selecting on a column that
is not indexed. You can use the EXPLAIN QUERY PLAN statement to find out
exactly how any given SELECT statement is processed by sqlite.

Even if you are sure that a large number of searches will be carried out on
the values of a particular column, it's not always a good idea to creat an
index on it.  It's a factor of how many unique values of the column exist.
 As an extreme example, if you have a table with a million entries, but the
column you search on only has 3 unique values, it's more efficient for
sqlite to do a serial search that to use an index.

There's really not a hard and fast rulke of when and when not to create an
index but in general unless the number of unique values in the indexed
column is around 15%-20% of the total number of records in the table, it
may not be worthwhile.  That's just a guideline and doing specific
comparison tests on the application is the only way to know for sure, and
even then the data charactersitics can change over time.

I'll come clean and say I often break that rule because I always define an
index on the child column of a foreign key relationship by default!

Pete
lcSQL Software <http://www.lcsql.com>



On Thu, May 31, 2012 at 11:35 AM, Bob Sneidar <bobs at twft.com> wrote:

> Yes, but it is my understanding of SQL that a query on a non-indexed
> column will create a temporary index in mySQL and maybe others. I might be
> wrong about that. But Foxpro does not do that. In fact if you set a filter
> or seek using a FOR statement that has no indexed columns or has some
> non-indexed columns, Foxpro will proceed at a reduced speed without any
> temporary indexing.
> Of course, you could index all columns that you will ever search on, but
> this is not always the best, because depending on the application, many
> columns may only occasionally be searched, and the overhead of updating all
> those indexes becomes counter-productive in terms of speed overall. So the
> principle of indexing columns that you search often, as you stated is the
> real life standard for indexing imho.
>
> Bob
>
>
> On May 31, 2012, at 11:19 AM, Ruslan Zasukhin wrote:
>
> > On 5/31/12 8:14 PM, "Bob Sneidar" <bobs at twft.com> wrote:
> >
> >> Foxpro does, but it's slow.
> >
> > You mean that foxpro can do
> >    search on column that is not indexed ?
> >
> > Well, of course each db engine can do this.  :-)
> > And Valentina, and Light, and mySQL, ...
> > And yes it is much slower than indexed search,
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>