Re: [sqlite] Search query alternatives.

I am glad I posted the question. Yes James, there is little I can disagree with in your excellent summary. Even the critique of my perhaps poorly framed question is indeed valid. I take your point regarding spec vs implementation, and in my experience across different rdbms's I have frequently

Re: [sqlite] Search query alternatives.

On Thu, 16 Oct 2014 09:05:51 +1100 Michael Falconer wrote: > we just wonder if there is a better way to perform this search in > SQL. Is there a general technique which is superior either in speed, > efficiency or load bearing contexts? The simple answer is No,

Re: [sqlite] Search query alternatives.

Michael, a Guy named Joe Celko elaborated about trees and hierarchies in SQL a few years ago. Have a look here: http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-Management/dp/0123877334/ref=dp_ob_title_bk regards gerd ___ sqlite-users

Re: [sqlite] Search query alternatives.

Thanks Eduardo, a most interesting link. On 17 October 2014 05:41, Eduardo Morras wrote: > On Thu, 16 Oct 2014 09:05:51 +1100 > Michael Falconer wrote: > > > Hi all, > > > > first off I must start with an apology. I know I'm sort of doing the >

Re: [sqlite] Search query alternatives.

On Thu, 16 Oct 2014 09:05:51 +1100 Michael Falconer wrote: > Hi all, > > first off I must start with an apology. I know I'm sort of doing the > wrong thing here as this question is NOT related to sqlite. It is a > general SQL question but I ask it here because I

Re: [sqlite] Search query alternatives.

Thanks Stephen, good point, I was just after some general results, I do take your point about caching etc. However it is logical to apply UNION ALL if appropriate in preference to UNION which in this context is a bit lazy. I'm a bit annoyed I didn't pick up on it myself, but thankful to Igor for

Re: [sqlite] Search query alternatives.

Careful with the timing. You may be looking at OS memory caching the result set instead of pulling from the drive. For best bets, either re-run both queries several times, ditch the longest and shortest times, then take the mean or average times and do the comparison that way. On Wed, Oct 15,

Re: [sqlite] Search query alternatives.

Igor, Nice one. A quick test using 10 lookup trees and the same search criteria: *Showing rows 0 - 29 (30 total, Query took 0.4838 sec)* Now with UNION ALL replacing UNION: *Showing rows 0 - 29 (30 total, Query took 0.2050 sec)* You weren't kidding about cheaper were you? LOL On 16 October

Re: [sqlite] Search query alternatives.

Yes, I agree with the general sentiment. It is not exactly the design I would have chosen either, but it is what exists. Design change is occurring but for the moment we are stuck with the current schema. If you cannot change the schemata to be more suitable, then your > demonstrated queries are

Re: [sqlite] Search query alternatives.

On 2014/10/16 00:05, Michael Falconer wrote: Hi all, first off I must start with an apology. I know I'm sort of doing the wrong//... No need to apologise, this flies quite close to the central theme. Whether you are using SQLite or any other SQL RDBMS, this is horrible DB design and it is

Re: [sqlite] Search query alternatives.

On 10/15/2014 6:05 PM, Michael Falconer wrote: addresses connections events family person repositories sources texts Personally, I'd have one set of tables, each with an extra column containing . Why do you need a separate set of tables, only to UNION them on every request? Instead of