On 9/9/05, Sylvain Lafleur <[EMAIL PROTECTED]> wrote: > > Table1 has around 29000 records. > Table2 has around 22000 records and links to table1 by > Table2.ID=Table1.Table2_ID > Table3 has around 3000 records and links to table2 by > Table3.code=Table2.code > > The query is something like: > > select * from table1 inner join table2 on Table2.ID=Table1.Table2_ID inner > join table3 on Table3.code=Table2.code where table2.english_descriptionlike > '%furniture%' > > I have set Primary key on table1.id <http://table1.id> <http://table1.id> > and > table2.id <http://table2.id><http://table2.id>as well as UNIQUE index on > table3.code. > > I can do a select from 1 table, no problem, but as soon as there is an > inner > join, nothing is returned and sqlite.exe takes 96-100% CPU until i end > process. > > Odly enough, everything was working 2 days ago, and I have tried > re-creating > the database from scratch. Same problem occurs with sqlite2 as with > sqlite3. > And also, like I said, the exact same query in SQLITEBrowser.exe works > fine > without a problem. > > The first 2 days the Rails applicatio was up, sqlite would return results > for '%furniture%' in about 1.2 seconds, now the results never come, almost > like sqlite hit an infinit loop. It does work fine whowever when i use the > following query: > > select * from table1 inner join table2 on Table2.ID=Table1.Table2_ID inner > join table3 on Table3.code=Table2.code where table2.id=10 > > I would really not want to merge all the tables together because table2 > links to about 6 other tables that are like table3 (i only like to 1 at > time, based on user input)
No query with " like '%furniture%' " is going to be fast. It's got to do a complete scan of all the records for this. --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
