LiveCode and SQLite performace

[Michael Doub]

Kay and Mark, the database file size is 250Mb.   The performance issue 
that surprised me was the open time.  Adds and Queries are as expected.

ID's currently range from 1 to 60400
URL is a typical URL that ranges from 50 to 130 characters, average is 
80 char.
tag is currently not being used so empty
local is a mac file path:  of the form /Volumes/EXTERNAL/XXX/xxxxxxxxx.xxx

How would I create and index?   When I look at the database with 
sqlitebrowser, it looks like an auto index was created.
Can you give me instructions as to how I should have created the 
database with an index?

Peter, This is using livecode 7.0.6 on a Mac OSX 10.10.4.   My DataBase 
expertise is very limited.  I created the database with sqlitebrowser, 
then added the data with livecode.   I don't know anything about PRAMAs 
or even how to execute them.   If you can provide instructions I will 
give it a try.

-= Mike




On 7/21/15 11:48 PM, Kay C Lan wrote:
> An example of half a dozen records please. Maybe a couple of very short, a
> couple of the longest and a couple of average.
>
> How big is the db file size - MB not record count?
>
> On Wed, Jul 22, 2015 at 7:55 AM, Michael Doub <mikedoub at gmail.com> wrote:
>
>> I need some database consulting help.   I have created an SQLite database
>> that has 4 fields:
>> ID, URL, tag, local.   IS is integer, unique, URL is text not null unique
>> and tag and local are just text.   I have 600,000+ records. I use the ID to
>> access each record in most cases or I am filtering trying to find records
>> based on the contents of the tag, URL or local fields.
>>
>> It seems to take forever to open the database.  This is normal?   I am
>> primarily using the database to make sure that I do not have non-unique
>> URLs, adding performance and selects seem reasonable.   I am just surprised
>> about the open performance  Does opening the database load a lot of
>> information into memory thus the long delay?   Could I have created the
>> database in someway that is not optimal, thus causing the delay?
>>
>> This is not a livecode issue as I am seeing the same delay when I open the
>> database with sqlitebrowser.
>>
>> Any advise or incites in how sqlite actually works would be appreciated.
>>
>> Regards,
>>     Mike
>>
>>
>> _______________________________________________
>> 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
>>
> _______________________________________________
> 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
>