Re: [sqlite] Managing SQLite indices.

[J Decker]

sqlite supports 'natural join' which allows you to omit the on clause for
well structured databases.

My database model is really a C# DataSet ( which contains DataTable, which
contain DataColumn and DataRow, (and this is superfluous, but row has a
reference to it's table and therefore the columns to relate to the array of
elements it contains) DataRelations are in DataSets between DataTables, and
they are foriegn key definitions; with OnUpdate, OnDelete, et al behaviors
definable.

I have a module that's open source that takes a data table and many common
operators and generates create table and constratint thigns for DataSets.
The DataSet CAN be designed using the dataset designer (it's OK; nothing
that you couldn't find better of in the 90's).


table1
   table1 + _id  --automatic primary key; I use object in the datatable now
as the type so the key can be GUID or int autoincrement.
   table1 + _name -- common text field that this ID might be printable in
part as... could be _description, _text, _ToString ?

table2
table2 + _id
table1 + _id

DataRelation( table2, table1.TableName+"_id", table1, table2.TableName
).onDelete = (?rule enum).Cascade. *shrug* too specific I know

which can be wrapped in something ilke makeDataRelation( table2, table1 );
automatically and consistently.

The problem becomes self-recursive keys which should themselves have just
tablename_id,  but would conflict with themselves...

map1
 map1_id int auto_increment PRIMARY KEY,
 parent_map1_id int
 node_info_id int

where it joins  ( parent_map1_id = map1_id )  ...

---
I did learn in school something like how to make words plural

public static string StripPlural( string s )
{
if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3,
"ies", 0, 3 ) == 0 )
return s.Substring( 0, s.Length - 3 ) + "y";
if ( ( s.Length > 3 ) && String.Compare( s, s.Length - 3,
"ses", 0, 3 ) == 0 )
return s.Substring( 0, s.Length - 3 ) + "s";
if ( ( s.Length > 1 ) && s[s.Length - 1] == 's' )
return s.Substring( 0, s.Length - 1 );
return s;
}


So I did add rules so I could take a plural table name like "Players" and
make Player_id, Player_name, etc...


And many tables had an ID that was an _info record or a _decription of
something and "member_info" to be member_id, etc...

public static string StripInfo( string s )
{
int trim = s.IndexOf( "_info" );
if( trim > 0 )
return s.Substring( 0, trim );
trim = s.IndexOf( "_description" );
if( trim > 0 )
return s.Substring( 0, trim );
return s;
}

We did try to suffix tables with sort of a data type; kind of the table
name is it's object-type  maybe there's no justification for chopping
it, and in correctness the full tablename shoudl be used so players and
player_info and player_desc don't all collide at some point; I could blame
it on having to conform to an existing system?  The new system was
certainly easy to automate and even work with by hand.


prefixes can be more useful a DataSet can have a prefix that gets
applied to all tables in it, so if you had a small cluster of information
like user_permissions, which has a grouping of tables, they could all share
a similar prefix and be located together when browsing.

(there are no relations between datasets, but table names would never
include their prefix when referencing them... )

Please do feel free to rip giant holes in anything I've said :)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[Scott Hess]

One thing I would add is to try to populate your example database with
representative data - in fact, try hard to figure out what
representative data looks like, it informs many decisions.  My
experience is that sometimes people assume that because something is
fast enough on their workstation, it's fast enough for production, but
in production their joins involving multiple full table scans are
suddenly generating tens of thousands of rows for their SORT to order
and their WHERE to filter, instead of the small constant number of
rows in their simple test database.

-scott


On Mon, Jun 13, 2016 at 5:04 PM, Simon Slavin  wrote:
>
> On 14 Jun 2016, at 12:27am, Smith, Randall  wrote:
>
>> the info from EXPLAIN QUERY PLAN and from reading the query itself don't 
>> always lead to an obvious statement of what indices are needed.
>
> I don't think this can be done well by software.  Of course, I haven't tried 
> it.
>
>> Has anyone figured out a good system for managing indices in a smooth, 
>> efficient, and reliable way in a non-trivial SQLite application?
>
> Sure.  But you're not going to like it.
>
> General principles:
>
> A) Plan your schema properly.  Tables represent things.  Think through 
> COLLATE for every column, especially key columns.  Use foreign keys rather 
> than copying data into other tables.  No need to be obsessive about it but 
> "that's how it was done when I started here" is not good enough.  Work out 
> your tables, your primary keys and your views and your indexes will take care 
> of themselves.
>
> B) If a query runs fast enough, it runs fast enough.  Don't mess with "as 
> fast as possible".  That way lies madness.
>
> C) Don't index a column just because it looks important.  You create an 
> index, when you create an index, for a particular statement. You look at the 
> "WHERE" and "ORDER BY" clauses and figure it out from there.  It's always 
> possible to create the best possible index for a statement by inspecting 
> those two clauses and thinking about how "chunky" each column is.  You may 
> not need the full index -- the rightmost column(s) may be unnecessary -- but 
> it's a good starting point.
>
> Got the principles ?  Right.  Now here's the procedure:
>
> 1) Delete all indexes.
> 2) Run ANALYZE.
> 3) Run your application.
> 4) Note the SQLite command which takes the most annoyingly long time.
> 5) Work out a good index which will fix the problem.
> 6) Create the index.
> 7) Repeat from step 2.
>
> When your application runs fast enough not to annoy you, you're done.  If 
> you're not willing to do step (1), don't bother with anything else.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[Simon Slavin]

On 14 Jun 2016, at 9:54pm, Smith, Randall  wrote:

> Thanks for the ideas, Simon.  Already good on the general principles.  The 
> approach of just periodically deleting all the indices and starting over from 
> scratch with a massive, comprehensive re-profiling effort might work on a 
> small project, an overstaffed one, one that doesn't change much, or one that 
> is not in a high-pressure environment, but is definitely not practical on 
> mine where none of these conditions apply.

In a situation like that you do not work on the production database on 
production hardware, but a test copy on test hardware.  But the principles are 
unchanged.

As I wrote, changing the data shouldn't change the indexes needed.  That 
happens only if you stop executing some SQL commands or start executing some 
new ones.  If you have a huge complicated schema with many different SQL 
commands executed perhaps it would be acceptable to do the process just for 
each new command:

1) For each new SQLite command, does it execute in acceptable time ?
2) If not, figure out an index which is suited to the new command.
Compare this new index and see if it's sufficiently different from existing 
ones to be worth creating just as it is.
3) If not, replace an existing index with one which incorporates elements of 
both.

An experienced SQL programmer would know a few indexes to create before even 
running their first test.  But doing the process I outlined will teach you how 
indexes work and let you gain enough experience to do that.

>   o Some kind of "gee, I sure wish I had this index" info from the query 
> planner. [and other suggestions which follow from those]

Given the above sequence, why not go the whole way and have the software 
automatically create the new indexes itself ?  Or have the SQL engine just make 
every temporary index it uses while running permanent ?  Then you wouldn't need 
the human at all.

Using the automated system outlined above just leads to databases with far too 
many indexes.  Consulting tables takes little time but making changes takes far 
more because for every row created/deleted many indexes need to be updated.

Another problem with the above procedure is that that new indexes made can make 
old indexes pointless.  You need a human to realise when that happens and weed 
out the old ones.  Or to start from a situation where you have no indexes at 
all.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[Smith, Randall]

Thanks for the ideas, Simon.  Already good on the general principles.  The 
approach of just periodically deleting all the indices and starting over from 
scratch with a massive, comprehensive re-profiling effort might work on a small 
project, an overstaffed one, one that doesn't change much, or one that is not 
in a high-pressure environment, but is definitely not practical on mine where 
none of these conditions apply.

One thing I've been curious about is why SQLite doesn't provide more powerful 
help here.  A few things I can think of that would be useful would be:

o Some kind of "gee, I sure wish I had this index" info from the query 
planner.

o Index utilization statistics, so little-used or unused indices could 
be identified and eliminated.

o Timing info for each step of the query plan (and for that matter the 
overall query), so query profiling would be easier and you could quickly 
identify the problem spot in a complicated query instead of having to 
constantly play find-the-peanut.

I'm no database engineer, so I have no idea how easy or hard these would be!

Randall.


> From: Simon Slavin 

> Has anyone figured out a good system for managing indices in a smooth, 
> efficient, and reliable way in a non-trivial SQLite application?

Sure.  But you're not going to like it.

General principles:

A) Plan your schema properly.  Tables represent things.  Think through COLLATE 
for every column, especially key columns.  Use foreign keys rather than copying 
data into other tables.  No need to be obsessive about it but "that's how it 
was done when I started here" is not good enough.  Work out your tables, your 
primary keys and your views and your indexes will take care of themselves.

B) If a query runs fast enough, it runs fast enough.  Don't mess with "as fast 
as possible".  That way lies madness.

C) Don't index a column just because it looks important.  You create an index, 
when you create an index, for a particular statement. You look at the "WHERE" 
and "ORDER BY" clauses and figure it out from there.  It's always possible to 
create the best possible index for a statement by inspecting those two clauses 
and thinking about how "chunky" each column is.  You may not need the full 
index -- the rightmost column(s) may be unnecessary -- but it's a good starting 
point.

Got the principles ?  Right.  Now here's the procedure:

1) Delete all indexes.
2) Run ANALYZE.
3) Run your application.
4) Note the SQLite command which takes the most annoyingly long time.
5) Work out a good index which will fix the problem.
6) Create the index.
7) Repeat from step 2.

When your application runs fast enough not to annoy you, you're done.  If 
you're not willing to do step (1), don't bother with anything else.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[Simon Slavin]

On 14 Jun 2016, at 2:29pm, John Found  wrote:

> you missed one small but very important 
> detail: 
> 
> "When your application runs fast enough...
> **on the slowest possible computer you can run it** 
> ...you are done."

A fair point.  Thanks for the niggle.  I might put it differently but certainly 
it's pointless to test for speed on your own custom-assembled 
must-compile-linux-in-20-minutes desktop.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[James K. Lowden]

On Tue, 14 Jun 2016 01:04:27 +0100
Simon Slavin  wrote:

> When your application runs fast enough not to annoy you, you're
> done.  If you're not willing to do step (1), don't bother with
> anything else.

Simon's entire post is excellent advice.  To the OP: print it, and
frame it.  

I would only add to 

> Plan your schema properly.  

the "properly" is code for BCNF or at least 3NF.  It's the gift that
keeps on giving.  

--jkl
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[John Found]

On Tue, 14 Jun 2016 01:04:27 +0100
Simon Slavin  wrote:

> Got the principles ?  Right.  Now here's the procedure:
> 
> 1) Delete all indexes.
> 2) Run ANALYZE.
> 3) Run your application.
> 4) Note the SQLite command which takes the most annoyingly long time.
> 5) Work out a good index which will fix the problem.
> 6) Create the index.
> 7) Repeat from step 2.
> 
> When your application runs fast enough not to annoy you, you're done.  If 
> you're not willing to do step (1), don't bother with anything else.
> 

Great algorithm I use all the time. But you missed one small but very important 
detail: 

"When your application runs fast enough...
**on the slowest possible computer you can run it** 
...you are done."

Neglecting this detail will always result yo sluggish programs on the
average user computer. 

That is why I am using netbook class PCs and 5..10 years old desktop machines
for testing my applications.

-- 
http://fresh.flatassembler.net
http://asm32.info
John Found 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Managing SQLite indices.

[Simon Slavin]

On 14 Jun 2016, at 12:27am, Smith, Randall  wrote:

> the info from EXPLAIN QUERY PLAN and from reading the query itself don't 
> always lead to an obvious statement of what indices are needed.

I don't think this can be done well by software.  Of course, I haven't tried it.

> Has anyone figured out a good system for managing indices in a smooth, 
> efficient, and reliable way in a non-trivial SQLite application?

Sure.  But you're not going to like it.

General principles:

A) Plan your schema properly.  Tables represent things.  Think through COLLATE 
for every column, especially key columns.  Use foreign keys rather than copying 
data into other tables.  No need to be obsessive about it but "that's how it 
was done when I started here" is not good enough.  Work out your tables, your 
primary keys and your views and your indexes will take care of themselves.

B) If a query runs fast enough, it runs fast enough.  Don't mess with "as fast 
as possible".  That way lies madness.

C) Don't index a column just because it looks important.  You create an index, 
when you create an index, for a particular statement. You look at the "WHERE" 
and "ORDER BY" clauses and figure it out from there.  It's always possible to 
create the best possible index for a statement by inspecting those two clauses 
and thinking about how "chunky" each column is.  You may not need the full 
index -- the rightmost column(s) may be unnecessary -- but it's a good starting 
point.

Got the principles ?  Right.  Now here's the procedure:

1) Delete all indexes.
2) Run ANALYZE.
3) Run your application.
4) Note the SQLite command which takes the most annoyingly long time.
5) Work out a good index which will fix the problem.
6) Create the index.
7) Repeat from step 2.

When your application runs fast enough not to annoy you, you're done.  If 
you're not willing to do step (1), don't bother with anything else.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Managing SQLite indices.

[Smith, Randall]

I'm developing a medium-sized SQLite-based app (maybe 50 tables, 400 query, 
insert, and update routines).  As we all know, having the right set of table 
indices is vital for getting decent performance from SQL.  However, I'm getting 
to the point on this project where this is getting to be very difficult.
 
Some issues (at least for me) are:
 
. Table schema tend to evolve over time.
. Queries are added and removed.
. Queries, table definitions, and index creation are often in different places 
in the code.
. There's no obvious way to identify indices that are no longer needed.
. Indices are confusing at the best of times, and the info from EXPLAIN QUERY 
PLAN and from reading the query itself don't always lead to an obvious 
statement of what indices are needed.
 
Has anyone figured out a good system for managing indices in a smooth, 
efficient, and reliable way in a non-trivial SQLite application?
 
(Apologies if this subject has already been covered; I just joined the list.)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users