0

i read some advice about table indexing, and i fount out that indexes should be introduced later when your sql queries are running slow..

my problem is I index almost all my columns in one table. this table is tied to other table, for example the users table column country is tied to the country table

user table          country table
id                  id
countryId           name

I added an index in the countryId column, so that I can link it to the country table's id because i can't create a foreign key constraints when I don't have an index on the column.

Basically i have many columns that are link to other table just to add constraints to my column.

for example, the country table is not more than 100, so if someone just accidentally inserted a value of 101, mysql won't accept that data because the country table only have 1-100..

So how can i avoid adding indexes and still have the function of table data constraints?

Kevin Lee
  • 1,079
  • 6
  • 17
  • 34

1 Answers1

1

  1. That advice is awful.

  2. Index columns that you will be searching on or where you need particular constraints such as unique values.

The only reason NOT to add indexes is that they do take up space. But, unless you're working with so much data that you'll need a database administrator, you probably won't have to worry about it.

evan
  • 12,307
  • 7
  • 37
  • 51
  • here's the link of the advice i read.. http://stackoverflow.com/questions/418744/what-is-the-cost-of-indexing-multiple-db-columns – Kevin Lee May 16 '12 at 04:54
  • the values are not unique, it is kind of a look up table constraint. but i will often look for it – Kevin Lee May 16 '12 at 05:04
  • The advice there was specifically for a "large" table. The table mentioned is actually still pretty small. The answer accepted mentioned adding indexes on any column where you get a full table scan - basically any column being searched against. As mentioned, when you get to the point of having a large enough table for any of this to matter, you'll probably want to get professional help from a database administrator. Until then, don't worry so much. – evan May 16 '12 at 05:27
  • Also, most of the other answers for the question say that "worrying about adding indexes" is premature - not indexing itself. From the second highest reputation responder, "With that kind of insertion rate, the cost of indexing an extra column will be negligible." – evan May 16 '12 at 05:35
  • Another motive to don't use indexes when you don't actually need it: avoid index_merge, that sometimes can give you a very bad performance. https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/ – Israel Fonseca May 03 '18 at 18:23