Re: [sqlite] performance impact of index creation order

[Simon Slavin]

On 20 Dec 2017, at 3:38pm, Nelson, Erik - 2  
wrote:

> Assuming that table 'data' is completely constructed, does index creation 
> order have any performance ramifications?
> 
> For example, would it be reasonable to assume that the order of these two 
> statements has no performance impact?  (all fields are integer in this case)
> 
> create unique index data_index_1 on path_data(id, path, date)
> create index data_index on data(path, date)

The 'id' column is unique.  And as id is also the primary key of the table 
(assuming you’re doing things the normal way) there’s already a UNIQUE index 
made on it.  So there’s no point in creating the first index.

As a general rule, once you have created all your indexes, the order in which 
indexes were created makes no difference to timing for future operations.

Once you have created all your indexes, and populated your tables with 
plausible data, run ANALYZE.  Although you can do this with empty tables, it 
can gather far more useful information once it sees the sort of data you put in 
your tables.

Once you’re run ANALYZE once there’s no need to run it again unless you change 
your schema or radically change the kind of data stored in you tables.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] performance impact of index creation order

[Nelson, Erik - 2]

Assuming that table 'data' is completely constructed, does index creation order 
have any performance ramifications?

For example, would it be reasonable to assume that the order of these two 
statements has no performance impact?  (all fields are integer in this case)

create unique index data_index_1 on path_data(id, path, date)
create index data_index on data(path, date)


--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users