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
===================================================
Thanks for the pointer Charlie, but I was only using * for my example, and I
normaly use fully named columns (table.column) when writing queries.
My new query, looks like this (generified):
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).
