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
