0
I have a table where the Primary Key user_id is used as query parameter, need to set this field as index to improve query performance? Or only as Primary key is sufficient for such?
0No answers
Browser other questions tagged sql
You are not signed in. Login or sign up in order to post.
Primary Key is already by default an index. But just in case, what is your DBMS?
– Don't Panic
Usually primary key is already created as an index, because searching for it is something very routine in the bank. Do you think your performance is bad? Which SGDB you are using?
– Jefferson Quesado
Also related: https://answall.com/q/216855/64969 Please see the first comment of the question
– Jefferson Quesado
I read the posts.. thanks.. I am loading the database to the server and start running the tests when I came across the configuration.. Field that is Primary key and set as index. I will delete the index setting and monitor during tests.
– RRV
But it is Mysql you are using?
– Don't Panic
Oracle for example does not allow two equal indices in the same table.
– Motta
No, you don’t have to. If you have already defined this column as a Primary key you are specifying that it is a column where the records do not repeat and therefore it should be used in your querys to filter data. However if there is any other table in the system, where there is a relationship with the user table, in this other table there must be the primary key of the user table that as defined is: user_id and this yes field must be defined as an index of this table.
– Rafael Salomão
@Everson I’m using Mysql... I removed some keys that are already Primary key.
– RRV
@Rafaelsalohand the child table field is called Foreign key and non-inclusion.
– Don't Panic
@Everson, READ CAREFULLY : "in this other table there must be the primary key of the USER table that as defined is: user_id" or it will be an FK. In Portuguese: FOREIGN KEY !
– Rafael Salomão