Jump to content
  • Advertisement
Sign in to follow this  
NeoJigglypuff

[web] regarding database indexes

This topic is 3449 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

From what I have read, the general idea in choosing indices is to choose what columns are accessed the most. Are the columns being mentioned here the ones being returned from the SELECT clause or those in the HAVING / WHERE clause? I also tried using composite indices and noticed that the ordering of the columns to be indexed produced different results. Why is that so?

Share this post


Link to post
Share on other sites
Advertisement
You want to index columns in your WHERE and ORDER BY clauses.

All an index is, is a pre-sorted list of your records, sorted in the order you specify. So if your data is like this:


+---------+----------+
| ColA | ColB |
+---------+----------+
| A | 3 |
| B | 2 |
| B | 4 |
| A | 1 |
+---------+----------+


Then an index on (ColA, ColB) would be (A,1) (A,3) (B,2) (B,4), whereas an index on (ColB, ColA) would be (A,1) (B,2) (A,3) (B,4).

If you did a "SELECT * FROM table WHERE ColA='A'" then the first index would help, because you just need to look until you hit the first value that's not an "A" but the second index would be no use, because the ColA columns are essentially random.

Share this post


Link to post
Share on other sites
Also keep in mind that an index may not be used if the database planning engine determines it wouldn't help.

For example, with a small enough table, it is actually quicker to load the table and scan it than it is to load the index, seek the values you need, and then load the parts of the table the index points to. Another reason it might not be used if is your index has low selectivity, meaning the index is not varied enough to be useful. Also some database engines need to have their index statistics updated periodically, although some do this automatically, or at least have an option to do it automatically.

The best thing to do is to check out the query plan generated by your database to see if your index is being used properly. If not you can tweak your queries or maybe redesign your tables or indices to get better results.

That said, Codeka gave you a decent rule of thumb. Just don't expect it to always just work, you might have to dig deeper sometimes.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

Participate in the game development conversation and more when you create an account on GameDev.net!

Sign me up!