On 7 Nov 2013, at 10:52am, Daniel Polski <[email protected]> wrote:
> I would be happy to get advice on how to think / what to look for when trying
> to optimize a query, views, adding indexes, optimizing schema design and so
> on.
>
> So far I've figured out that I could add appropriate indexes since the
> 'explain query plan' indicates the query is using many automatic indices, but
> I haven't added any extra yet since I would like to provide you with the
> original schema.
Not about to go through the whole thing, but a few things jump out at me.
Mostly that this is too big and too complicated and looks like you're trying to
keep your entire business logic in a database. While technically your final
query_view might work, the fact that it is so complicated suggests that your
database design originated back in the mainframe days. Anyone trying to
understand your schema or any software that relies on it is going to be
completely lost. As someone who has had to work on code written 20 years ago
by people long since retired, that setup concerns me.
In short, the reason it's not obvious to you what indexes are going to be
useful is that your design is too complicated and relies on too much inside
knowledge.
* Tables t11 and t12 look like they should be the same thing, or related in
some way.
* SQLite does not have a DATETIME type.
* Don't use single quotes (or even double quotes) to delimit constraint names.
Single quotes are for specifying strings.
* Your date/times in t13 should probably be encoded as a startDateTime and an
endDateTime. Having to search/match both on date and time makes stuff
unnecessarily complicated.
* Special use for some id values in t1 and t3 and t5. Looks weird. If these
are two different kinds of things they should be marked differently (have
different values in a column) or exist in different tables or something, not
rely on some special internally-known business logic that knows that 16, 32,
1000 and 2000 are magic numbers. Think through why those rows are so special
and figure out a way to use tables and columns to do it. Or keep that logic
entirely outside your database definitions.
* TRIGGERs t1 --> t1 --> t2 --> t4, t3 --> t3, t5 --> t5, t7 --> t7, t16 -->
t16. Anything that complicated is bound, sooner or later, to collapse. If you
intend to keep level of complexity you need to write some database-checking
code which checks the integrity of your database cross-references and makes
sure none of those trigger got violated. Generally speaking any code which
uses both FOREIGN KEYs and TRIGGERs shows a partially-completed conversion from
one schema to another.
* If your demo code needs to turn recursive triggers off, you're probably doing
something wrong somewhere. If those recursive triggers reflected how things
work in the real world, you would never need to turn them off.
* don't rely on the results of anything like "strftime('%s', datetime('now'),
'localtime') - strftime('%s', temp_table.starttime) AS timer". Definitions of
'now' within SQL are shaky at best (does it change during a SELECT that takes
10 minutes to execute ?). Things like that should be done in your software,
not in your database.
Dan, I'm sorry to be so complaining of your setup but it so strongly reminds me
of COBOL and other things I haven't seen since mainframe days. I suspect that
working to remove some of your internal business logic (magic numbers
especially) will force you to simplify your tables to the point that your final
SELECT will so simple you can figure out indexes by yourself.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
