0

So I am creating a website that has multiple topics, people post content in those topics and people can comment on the content. People can also comment on comments, but only one level deep. So there will be comments and subcomments and nothing else. All the comments on the subcomments will be listed as subcomments to the main comment. I don't like 20 levels deep comments where each level is marked by indentation. It ruins the look of my page.

Now I am thinking of 3 tables here. One table for content which will have content number as well as topic number. A comment table with comment number, content number and topic number field. A subcomment table with subcomment number, comment number and content number and topic number.

Now I am thinking about what my optimal strategy should be when assigning indexes in the subcomment table. Should I assign subcomment id alone a index or should I assign subcomment id, comment id, content id and topic number all the four a Index?

user17282
  • 497
  • 5
  • 13

2 Answers2

2

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first 2 columns, the first 3 columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Many thinks that we can just create each indexes based on the columns used in query. But that’s not true. But, in reality, nature of query should originally direct to the proper way of construction. this is the original rule of thumb:

  • If the columns in ‘Where’ each condition are constructed using ‘AND’ conditions, it’s better to create multi-column composite index.
  • If the columns in ‘Where’ each condition are constructed using ‘OR’conditions, it’s better to create multiple index based on eachcolumn.
  • clustered indexes should have a unique key(an identity column I would recommend) as the first column. Basically it helps your data insert at the end of the index and not cause lot's of disk IO and Page splits.
  • if you are created other indexes on your data and they are constructed cleverly they will be reused.

e.g. imagine you search a table on three columns

state, county, zip.

you sometimes search by state only. you sometimes search by state and county. you frequently search by state, county, zip. Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used(by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.

jack-nie
  • 736
  • 6
  • 21
0

I generally would index anything you are likely to be running a query from, especially since this a comment board and if you hope for any traffic at all, not indexing them could seriously slow down the rendering of the comments.

Edit: The decision really depends on how you expect the human element to respond. The site will load faster with more indexes, but updates might be a little slower. As multiple people are unlikely to be updating information at the same time, but people generally want to read first, I would lean on the faster select time.

nomistic
  • 2,902
  • 4
  • 20
  • 36
  • Is there any harm in making too many indexes? Like is there any trade-off? – user17282 Apr 30 '15 at 00:04
  • more indexes will speed up selects, but slow down inserts, updates and deletes. Basically you just need to decide whether it's more important to retrieve data then update it. Think of the use-case model. In this particular case, I would lean heavily on faster selects; you don't want people to leave your site while they wait for it to load. If they are commenting? they already want to be heard. – nomistic Apr 30 '15 at 00:08
  • Here's a reference: http://stackoverflow.com/questions/4120160/mysql-too-many-indexes – nomistic Apr 30 '15 at 00:11