UPDATE:: I just did in Windows:
1) sqlite3.exe database.db .dump > database3.sql 2) sqlite.exe database_v2.db < database3.sql 3) sqlite3.exe database_v3.db < database3.sql Results: -------- Case: sqlite.exe database_v2.db select * from table1 inner join table2 on Table2.ID=Table1.Table2_ID inner join table3 on Table3.code=Table2.code where table2.english_description like '%furniture%' Returns results in about 1.2 seconds -------- Case: sqlite3.exe database_v3.db select * from table1 inner join table2 on Table2.ID=Table1.Table2_ID inner join table3 on Table3.code=Table2.code where table2.english_description like '%furniture%' CPU goes to 96-100% and never returns until I end process. Conclusion: Something fishy going on. Same exact query but freezes with sqlite3.exe. I'm not sure if it's only in sqlite3 because the problem DID also happen with sqlite.exe however, sqlite.exe seems to work fine now. Any ideas? Thanks, Sly On 9/9/05, Jay Sprenkle <[EMAIL PROTECTED]> wrote: > > > > 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_IDinner > > 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_IDinner > > 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 >
