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_description like '%furniture%' I have set Primary key on table1.id <http://table1.id> and 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) Thanks Sly On 9/9/05, Bob Dankert <[EMAIL PROTECTED]> wrote: > > Hi Sly, > > Can't really help without the query that you are using and more > information about the database. Are you using indexes? How many > records are in the tables? > > Bob > > Envision Information Technologies > Associate > [EMAIL PROTECTED] > v. 608.256.5680 > f. 608.256.3780 > > -----Original Message----- > From: Sylvain Lafleur [mailto:[EMAIL PROTECTED] > Sent: Friday, September 09, 2005 1:05 PM > To: [email protected] > Subject: [sqlite] Windows SQLITE3 SUPER SLOW > > Hello, > > Sorry if this isn't correct, it's my first post in a mailing list. > > My problem: I created a Ruby on Rails application with sqlite as the > database. Everything worked for about a day. Now, any query with a inner > > join takes forever (i have yet to actually have results returned). When > i > pop the query in the sqlite2.exe, same problem, it's like there is an > infinite loop. However, when i use the program "SQLITE Browser > 1,2.1.exe", > the same query returns results instantly. Does anyone have any clue what > is > going on here. I hope i can fix it and still use sqlite. > > Thanks > > Sly >
