On 15/04/2009 11:40 PM, Kelly Jones wrote: > I have a hideous query that looks like this: > > SELECT anf.name AS child, anf2.name||anf3.name||anf4.name AS parent > [...] > WHERE child='albuquerque' AND parent='newmexico'; > > which takes forever to run.
I'm guessing: no column names in your WHERE clause, no index used. It certainly can't use 'parent' as it involves multiple tables. Perhaps with a bit of extra optimisation it could be made to realise that 'child' is a synonym for 'anf.name' However, when I replace 'child' with > 'anf.name' in the WHERE clause, it runs lightning fast (as expected, > since I have tons of indexes). > > Even more bizarrely, when I create a view: > > CREATE VIEW childparent AS > SELECT anf.name AS child, anf2.name||anf3.name||anf4.name AS parent > [...] > > and then do: > > SELECT * FROM childparent WHERE child='albuquerque' AND parent='newmexico'; > > it's again very fast. I'm guessing again: subtly different logic when processing views. Cheers, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
