LC vs mySQL

[Peter Haworth]

I think it's worth saying that indexes don't necessarily result in faster
retrieval and sometimes aren't used even when you think they would be.

In general, the fewer unique entries there are in an index, the less likely
they are to speed up retrieval. To take an extreme example,  if the indexed
column has the same value in every row,  the index is a waste of time. Hard
to lay down hard and fast rule, but I usually don't bother with an index
unless the average number of rows for each of it's values is no more than
15-20% of the total rows in it's associated table.

A common example of an index not being used is if you use the LIKE
operator. Let's say you have an index on a column named City and your where
clause includes

City LIKE '%town'

The index won't be used because all it's entries would have to be scanned
to satisfy the '%'. On the other hand

City LIKE 'Black%'

would use the index because only the entries that begin with 'Black' need
to be accounted for.

I've seen extreme examples where there would be a column that held the
contents of City spelled backwards because the DB designer knew there would
be a lot of queries in the first form above so the query could now be

ReverseCity LIKE 'nwot%'

Pretty weird!

Don't know about MySQL but sqlite has an EXPLAIN QUERY PLAN statement that
outputs information about how a query will be executed that is really
useful when trying to figure out if an index is justified.

Pete
lcSQL Software
On Dec 7, 2013 9:54 AM, <jbv at souslelogo.com> wrote:

>
> >
> > Indexing is good. You'll lose a little time on writes because of the
> > need to update the indexes, but more than make it up on reads. Can you
> > move some of the processing into stored procedures on the backend? The
> > more processing you can hand off to the MySQL server, the faster your
> > response time will be.
>
>
> AFAIR I had some bad surprises in mySQL indexes, especially fulltext
> indexes
> because of reserved words, or words so common in english that they aren't
> included in the index. The situation gets even weirder with french content
> of
> DBs when some words are used both in english & french...
> As for stored procedures, this is tempting, but maintenance gets harder
> when
> parts of the code are split between mySQL tables and LC scripts...
>
> jbv
>
>
> _______________________________________________
> 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
>