On Wed, Aug 7, 2013 at 2:54 PM, Christopher W. Steenwyk <csteen...@gmail.com > wrote:
> Hi, > > I have been working on a large database and its queries now for several > weeks and just can't figure out why my query is so slow. I've attached the > schema, my query, and the results of EXPLAIN QUERY from sqliteman. > > A few notes about this database: > Its approximately 10GB in size, but I have it on a SSD on a linux machine > with 12 GB of RAM on a 24 core PC. > > As for data in the tables... > 'Objects' has 350000 rows > 'frames' has 51896158 rows > 'attribute_types' has 50 rows > 'attribute_values' has 200 rows > 'metrics' has 68682102 rows > > For every object there are approximately 5 rows that relate to it in > 'object_characteristics' and another 20 rows in 'metadata'. > > The attached query takes over 6 days to run. > > Any help or suggestions would be greatly appreciated. > Can you please send the output of ".dump sqlite_stat1". Also, if you can run sqlite3_analyzer on the database file and send us that, so much the better. Thanks. > > I noticed that the part of the query for 'frames' is not using a covering > index, cut I can't figure out why. I Was wondering if that is why it was > slow. In the query I am only referencing items that are within an index but > it keeps using the PRIMARY KEY. So that was one thought I had. > > Thanks in advance! > Chris > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
