All geek questions in one place

Does an additional SQLite multi column primary key need an additional index?

If I create a table as follows:

CREATE TABLE something (column1, column2, PRIMARY KEY (column1, column2));

Neither columns nor columns2 are unique in themselves. However, I will do most of my queries in column1.

Does the primary key of multiple columns create an index for both columns separately? I would think that if you specify a primary key with multiple columns, it will index them together, but I really don't know.

Was there any performance advantage to add UNIQUE INDEX in column1?

+4
source share
2 answers

There will probably not be a performance improvement because queries from col1=xxx and col2=yyy will use the same index as queries like col1=zzz without mentioning col2. But my experience is only Oracle, SQL Server, Ingres and MySQL. I do not know for sure.

+1
source

You certainly do not want to add a unique index to column 1, as you just said:

Neither columns nor columns2 are unique in themselves.

If the first column comes first, it will be the first in the multi-column index in most databases, and therefore it will be used. The second column is the one that cannot use the index. I would not add one to the second column, if you did not see problems and again, I would add an index not a unique index based on the comments you wrote above.

But SQL lite should have some way to see what it uses, like most other databases, right? Install Pk and see if only column1 uses its queries.

+1
source

Source: https://habr.com/ru/post/1313016/

More articles:


All Articles