> SQLite Version 3.2.2.
> Is this a bug, or is my SQL that bad?
> Query 1:
> 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));
> This query (and even more complex versions of it) works in MySQL (Haven't
> tried another DB yet) and I'm trying to migrate to SQLite, this is really
> holding me back.
The like clause is going to force it to do a full table scan (it will
have to read the
whole database every time). If you can find a way around that it will
help a lot.
Is there an index on the id column(s)? That might help some too.
======================================================
I don't think the LIKE is the problem, I used = there too. All the id
columns a primary keys, so I assume that means they're indexed well enough.
Update on 5 table query:
I can now do the query with INNER JOINs, and it returns instantly with the
correct results. The problem appears to be the order of the tables in the
JOINs. The first table is large (4000 odd rows) and it has absolutely no
conditions on it (the second doesn't help either). What I should have done
was have the tables that result in not many rows first, then add others as I
go.
Thanks for the ideas, I think I have it now (think).
- Very odd, this email was originally addressed to the poster, not the list
e-mail address
