DB Foreign Key / Pivot Table Question

Just on a side note, shouldn't

$table->bigInteger('country_id')->foreign()->references('id')->on('timezones');

be something like

$table->bigInteger('country_id')->foreign()->references('id')->on('countries');

Foreign keys are not really for cascading (it's an option but not really good because it could be dangerous ^^). The real interest is for referential integrity.

Index are needed on fields where you make many queries, to get them faster. But it also takes time to update index ^^.

@bestmomo thanks for the insight.

Question (and let me apologize in advance for my lack of DB knowledge, been working through my-sql site, but it is pretty much useless other than basics)

1. do I need to index foreign keys? or should I? Example, above, would I place an index i.e. ->index(), on any of the ones above or is foreign key in itself an index?
2. I believe the answer is no, but to confirm. Is it it a best practice to always place a pivot table on foreign indexes or do you only place them on a high transactional columns. If that is the case, would user_id in a system always be multiple pivots for every foreign relationship, as an example?
3. Again, I believe the answer is no, but there is not need to match index columns in one table with that in another? Example, if company_id in another table there is no reason to have both company id and company_id indexed? Unless, per your point company_id was a high query column. And I'm assuming there is no mutual benefit i.e. do I get the benefit of index on company id, let's say in user table with a column company_id?

It's interesting to set index on fields used for WHERE, ORDER BY, GROUP BY, MIN(), MAX(), and also fields for relations because of the JOIN. So foreign keys are good candidates for index.

If you really want to know is an index is usefull you can use EXPLAIN.

@nolros A few things (talking MySQL):

1. foreign key columns are indexed. You don't need to specify anything in the migration, MySQL will do it, no exceptions.

2. primary key and foreign key field types must match, otherwise you get db error when creating the constraint (running migration). Thus this might be wrong:

   $table->integer('parent_id');
   $table->bigInteger('industry_id');
   $table->bigInteger('timezone_id');
   $table->bigInteger('country_id');
   

   unless your id columns on the referenced tables are integer and bigInteger accordingly. Mind that default increments('id') create unsignedInteger type.

3. All your foreign keys fail silently, ie. migration runs w/o errors but doesn't create the foreign key constraints at all. You want this:

   $table->integer('parent_id'); // or unsignedInteger like mentioned above
   $table->foreign('parent_id')->references('id')->on('companies');
   

4. Pivot tables are required for many-to-many relationships, end of story. You can use them in a few other cases, like self referencing relationships etc, but these are edge-cases - don't bother.

5. Indexes are good for big tables. It's not something you can answer just like that - index here, index there. Sometimes index increases performance greatly, sometimes it's redundant. Single column indexes are used in where clauses, order by and group by clauses, min/max. Compound indexes are a bit different, ie. the order of specified columns matters, also leftmost columns must be used and so on and so forth. To sum up, use index on columns that are likely to be used in where on the tables that will grow. Then, when the time comes that you need to optimize your queries, read http://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html and make adjustments ;)

@JarekTkaczyk funny you mention failing silently. Inspecting the tables led to that exact issue. Here is what happened. Need to match types or it will simply fail.

btw guy thanks for the read. The indexes read has been a great read. very helpful!

Action  Keyname Type    Unique  Packed  Column  Cardinality Collation   Null    Comment
Edit Edit   Drop Drop   PRIMARY BTREE   Yes No  id  0   A   No  
Edit Edit   Drop Drop   companies_tax_id_unique BTREE   Yes No  tax_id  0   A   Yes 
Edit Edit   Drop Drop   companies_vat_id_unique BTREE   Yes No  vat_id  0   A   Yes 
Edit Edit   Drop Drop   companies_user_id_index BTREE   No  No  user_id 0   A   No  
Edit Edit   Drop Drop   companies_name_index    BTREE   No  No  name    0   A   No  

Jarek answers are always so very clear, didactics and complete !