Thanks alot,
I will read through your email in detail later and think it through, but
want to send a quick reply right now.
About complexity: I see that it looks overly complex, especially when
using table names like 't1' instead of names which makes sense in the
real world. I understand that it's hard to give any good advice here
without seeing the real usage. I do think that the design is fairly
"good" though, basically 3nf.
About triggers: They're only used in this example to fill the database
with some test data and is removed after they've been used.
About t11, t12: You're correct that they're doing something related, but
is separated on purpose to let the user link data different ways
Thanks alot!
/Daniel
Simon Slavin skrev 2013-11-07 14:08:
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
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
