I do not know much about database optimization, but I am trying to understand this matter.
Say I have the following table:
Cities =========== state_ id integer name varchar (32) slug varchar (32) < / Code> Now, say that I want to do Cuban like this:
Select from WHERE state_id = 123 and slug = 'little' selection * From city to WHERE state_id = 123 If I want the "Slug" for a city to be unique within its particular state, I will add a unique index on state_id and slug .
Is this indicator enough? Or should I add another on state_id so that another query is optimized? Or does another query automatically use unique index?
I am working on postgresql, but I think this matter is so easy that most DBMS works like this.
In addition, I know this definitely does not matter on small tables, but my example is a simple 200k + rows to think of tables.
Thank you!
On a single unique index (state_id, slug) must be sufficient. Definitely, of course, you will need to run with Explanation and / or Analyze (perhaps with some help), but eventually the indexes are suitable, depending on which type of questions you want to run, it depends very closely. Remember, indexed SELECTS slows down and slows down INSERTs, UPDATEs, and DELETE, so ideally you just want a lot of indexes like it really is necessary.
In addition to this, PostgreSQL is a smart query optimizer: it will use a fundamental different search for inquiries about small tables and giant tables if the table is small, it will only scan a sequential scan and Do not bother any index, because working with them is more than just brutal-force sips through the table. After having a threshold close to the table size, it changes to a different plan, and if the table re-grows again, or if you select or change your SELECT or change it then you can change it again ....
Summary: You can not rely on the explanation results as compared to your actual data and do very little or different analysis on the dataset. Work it out, then make it faster later (if you need it).
Comments
Post a Comment