Max, Thank you for your fast answer.
I already knew the thread you suggest. The issue here is that the same join works fine on 3.7.2 with a normal table: SELECT news1.number, news2.title FROM (SELECT number FROM news LIMIT 50) as news1, news2 WHERE news1.number=news2.docid When you join with a FTS3 table (a virtual table) then you have a big slowdown that did not happend in 3.6.23. SELECT news1.number, fts_news.title FROM (SELECT number FROM news LIMIT 50) as news1, fts_news WHERE news1.number=fts_news.docid Takes 8 seconds in 3.7.2 instead of 15ms in 3.6.23 or 3.7.2 with a regular table. Dan already suggested the LEFT JOIN workaround (Thanks Dan! :). Thanks again! :) Jochi MartÃnez www.bfreenews.com -------------- > Hi, > > Given this simple query with a subquery in FROM and a join with a FTS3 > table: > > SELECT news1.number, fts_news.title > FROM (SELECT number FROM news LIMIT 50) as news1, fts_news > WHERE news1.number=fts_news.docid > > The query runs in 15ms in 3.6.23. The same query runs in *8 seconds* in > 3.7.2. > > Jochi, looks like this is intentional behavior of inner join optimizer, there was a post recently, you can read about it here: http://www.mail-archive.com/sqlite-users@sqlite.org/msg55407.html Richard suggested using ANALIZE, other workaround is to use LEFT JOIN instead of INNER JOIN: SELECT news1.number, fts_news.title FROM (SELECT number FROM news LIMIT 50) as news1 LEFT JOIN fts_news ON news1.number=fts_news.docid in the thread mentioned you can read very details explanation about the logic introduced in v 3.7 up Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
