Re: [sqlite] Troubleshooting query performance

2014-03-05 Thread Joseph L. Casale
> Don't put the PRIMARY KEY as the first column of your index. Ever. This > applies to all SQL database engines, not just SQLite. > > For that matter, don't put the PRIMARY KEY anywhere in your index. The > PRIMARY KEY will be added automatically at the end, where it belongs. > > If you

Re: [sqlite] Troubleshooting query performance

2014-03-05 Thread Simon Slavin
On 6 Mar 2014, at 12:20am, Joseph L. Casale wrote: > SELECT a.name, p.value > FROM p_attribute p > JOIN attribute a > ON a.id=p.aid > WHERE p.pid=? > > This returns all relevant rows I need, where table profile has ~6000 rows, > p_attribute has ~ 170k and

Re: [sqlite] Troubleshooting query performance

2014-03-05 Thread Richard Hipp
On Wed, Mar 5, 2014 at 7:20 PM, Joseph L. Casale wrote: > Hey guys, > > I have a query that's giving me abysmal performance and it's not > immediately > obvious to me as to what's wrong with the table structure to cause this. > > CREATE TABLE profile ( > id