Here is the data from stat1: "tbl", "idx", "stat" "metrics", "metrics_idx", "68682102 2 2 2" "metrics", "metrics_frame_idx", "68682102 2" "metrics", "sqlite_autoindex_mobileye_ldw_metrics_1", "68682102 230 1" "object_characteristics", "object_characteristics_idx", "1148344 164050 31899 1" "object_characteristics", "sqlite_autoindex_object_characteristics_1", "1148344 4 1" "metadata", "metadata_type_value_idx", "510520 11873 2716 1 1" "metadata", "metadata_idx", "510520 4 3 1 1" "metadata", "sqlite_autoindex_metadata_1", "510520 4 3 1" "attribute_values", "sqlite_autoindex_attribute_values_1", "198 1" "attribute_types", "sqlite_autoindex_attribute_types_1", "50 1" "frames", "frames_idx", "51896158 11492 4052 1" "frames", "sqlite_autoindex_frames_1", "51896158 11492 4052 1" "objects", "objects_grade_idx", "350060 14"
I am working on running analyze. So, looking into EAV, I understand the drawbacks, but how would you recommend me restructuring my schema to be more efficient? Perhaps have an "Objects" table that has a column per attribute, and then a "frames" table that also contains the contents of metadata as separate columns for each value? That would reduce me down to three tables. Or just dump everything into one, massive table? This database is generated once, and then queried and interrogated multiple times. So I am most concerned with read speed and not with writing or updating. On Wed, Aug 7, 2013 at 6:44 PM, Richard Hipp <d...@sqlite.org> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
