Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 14:51:13 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > Well the query isn't that strange, I have 3 values that need matching > per-record returned, all in different tables (takes care of 3 tables) the > actual data I want is stored in another related table, and 2 of the values > that need to match are not related directly, so a 5th table is required for > that relationship. > Anyway, my original query does infact work, however it takes 10 minutes to > run. I'm guessing this is because it generates 1,530,200,100 rows of data to > do the WHERE clause on (just did a count(*) on it in mySQL) . Why MySQL can > do this so much faster, 1/10th of a second I dont know (though I am using > InnoDB not the default MyISAM engine. I image the BDB engine would be even > faster). > Just MySQL is to heavy to install with my application. Anyway, back to the > drawing board for an efficient way to get my data. Thanks again. I think if you try this with MySQL MyISAM you will have the same result and I think this is to do with the SELECT * I remember I used to have something similar back in the days when I tried MySQL. Charlie
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 13:06:42 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > SELECT count(*) FROM table1 > INNER JOIN table2 ON (table1.id = table2.rel_id) > INNER JOIN table3 ON (table3.rel_id = table2.id) > INNER JOIN table4 ON (table3.id = table4.rel_id) > INNER JOIN table5 ON (table5.rel_id = table4.id) > WHERE table1.value = 'value1' > AND ((table3.value LIKE '%value3%' AND table5.value = 'value5') > OR (table3.value LIKE '%value3%' AND table5.value = 'value5')); > > This query executes in 0.02s on MySQL, but about 14s on SQLite (to return 34 > rows). > I think the problem is the size of the temporary table the JOIN creates > (37,434) which is probably the issue. There is (7,579) records in my largest > table. I'm not sure what my best option would be to get this size down. But > I'm thinking along the lines of some nested SELECTs to get needed rows in > stages rather than all in one go at the end. My application is probably > going to get much larger data wise than it already is and MySQL is not > really an option anymore. Could possibly go back to UNIONs also if the OR > operation isn't as efficient as two SELECTs (though I highly doubt that is > the case). Run this through the analyser (EXPLAIN) which will show where the time is spent. I would also suggest trying PostgreSQL as my experience with MySQL is that performance degrades significantly with lots of JOINs as it just generates lots of Cartesian products and I think SQLite is the same, ie. a huge table is generated in memory and the conditions are applied to this row by row. Certainly seems a strange query, though. Charlie
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 10:58:04 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > Thanks Charlie, thats exactly how I should be doing it. I would imagine > there are some performance benefits from doing it this way too. Now I've > just got to make it work for my select that involves six not two tables! > Looks like I'll be getting my thick SQL book out for a while. SQL in a Nutshell is pretty good. There shouldn't be any performance benefits if the engine is working properly but it is emminently more readable. You may well hit problems with your SELECT * approach with names colliding, especially when you start adding UNIONs. It is always advisable to use explicit relational variable names (columns). Charlie -- Charlie Clark Communications Manager yellowTAB GmbH Tel: +49-211-600-3657 http://www.yellowtab.com mailto: [EMAIL PROTECTED]
Re: [sqlite] Query locking up SQLite
On 2005-07-25 at 10:15:14 [+0200], David Fowler <[EMAIL PROTECTED]> wrote: > SELECT * FROM table1, table2 > WHERE (table1.value LIKE "%value%" AND table1.table2_id = table2.id); > This query works perfectly, can't fault it. > But when I do this, SQLite locks out (Command line interface, and PHP5) > Query 2: > SELECT * FROM table1, table2 > WHERE ((table1.value LIKE "%value%" AND table1.table2_id = table2.id); > OR (table1.value LIKE "%different_value%" AND table1.table2_id = > table2.id)); I think this is a good example of why I use explicit JOINS. SELECT * FROM table1 INNER JOIN table2 ON (table2.id = table1.table2_id) WHERE table1.value LIKE '%value%' OR table1.value LIKE '%value%') Doesn't this do what you want? -- Charlie Clark Communications Manager yellowTAB GmbH Tel: +49-211-600-3657 http://www.yellowtab.com mailto: [EMAIL PROTECTED]
Re: [sqlite] I still think it's a bug, 0 rows, no fieldnames
On 2005-07-24 at 16:11:26 [+0200], Edwin Knoppert <[EMAIL PROTECTED]> wrote: > Why defending it? > It's prob. a simple issue. > Besides, why i want the names, why should a system need to parse a query? > You approach this way to 'static' imo (what you enter is your result). > You can easily say to also remove the fieldnames when we *have* data. An 0-row containing the relational variables is a *special* case and non-relational and causes problems for results which do not expect the names of the values to be included in the result set. .schema(table) gives you all you need. Charlie
[sqlite] Problem with building sqlite3
Dear list, I am trying to build Sqlite3 on ZETA but am having trouble disabling Large File Support. Of course, ZETA supports very large files but not in the way expected. What is the best way to do this? I thought it might be with some setting for configure but I haven't got this to work yet. Thank you very much Charlie Clark
