I'm trying to construct a database by using laravel relationships. As far as i know, foreign keys are a good way to ensure data integrity and correctness. Why not? I'd want to use it.   My questions are :  a)does using foreign keys in mysql really slow the performance at all? if yes, why does it slow?  b) If it slows, what should I do? Is it better not to use it at all ?
123125
Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query.  SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category FROM Products p INNER JOIN ProductCategories c ON p.CategoryId = c.CategoryId WHERE c.CategoryId = 1;   When I create execution plan, table ProductCategories performs cluster index seek, which is as expectation. But for table Products, it performs cluster index scan, which make me doubt. Why FK does not help improve query performance?  So I have to create index on Products.CategoryId. When I create execution plan again, both tables perform index seek. And estimated subtree cost is reduced a lot.  My questions are:   Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance? Should I create index on all FK columns (liked Products.CategoryId) in all tables?
14054
Recently I have been receiving down-votes because I do not correct the OP's answer to use "best practice." This has occurred on a few occasions regarding implicit or explicit syntax or deprecation of functions. For this question, I am specifically referring to  regarding deprecation of mysql_ functions.  In a question, the OP posts code that uses the mysql_ function and I post a correct (accepted) answer using those functions and inform the OP regarding the deprecation and alternatives. I would then receive a down-vote because I apparently have not sufficiently discouraged the OP's use of these functions.  Stack Overflow's  states:     Answer the question      Read the question carefully. What, specifically, is the question asking for? Make sure your answer provides that – or a viable alternative. The answer can be "don’t do that", but it should also include "try this instead". Any answer that gets the asker going in the right direction is helpful, but do try to mention any limitations, assumptions or simplifications in your answer. Brevity is acceptable, but fuller explanations are better.   Going off the Answer the question guidelines, I know that my answer provides exactly what the OP is asking as well as a "use this instead".   Stack Overflow's  states:     What is voting down?      Voting down, also known as "casting downvotes", is how the community indicates which questions and answers are least useful.      When should I vote down?      Use your downvotes whenever you encounter an egregiously sloppy, no-effort-expended post, or an answer that is clearly and perhaps dangerously incorrect.   Following the above guidelines, I don't feel my answers meet any of the criteria for voting down.  I certainly attribute down-votes with a negative connotation. I ask, to what degree should "best practice" affect down votes?
8186
is there an alternative for mysql_insert_id() php function for PostgreSQL? Most of the frameworks are solving the problem partially by finding the current value of the sequence used in the ID. However, there are times that the primary key is not a serial column....
40421
What are the best practices for modeling inheritance in databases?  What are the trade-offs (e.g. queriability)?  (I'm most interested in SQL Server and .NET, but I also want to understand how other platforms address this issue.)
12526
Would you recommend using a  or a  field, and why (using MySQL)?   I'm working with PHP on the server side.
97492
I have a MySQL database table with nearly 23 million records. This table has no primary key, because nothing is unique. It has 2 columns, both are indexed. Below is its structure:    Below is some of its data:    Now, I ran a simple query:  SELECT `indexVal` FROM `key_word` WHERE `hashed_word`='001'   Unfortunately, this took more than 5 seconds to retrieve the data and show them to me. My future table will have 150 billion records, so this time is very very high.  I ran the Explain command to see what's going on. Result is below.    Then I ran the Profile using below command.  SET profiling=1; SELECT `indexVal` FROM `key_word` WHERE `hashed_word` = '001'; SHOW profile;   Below is the result of the profiling:    Below is some more information about my table:    So, why this is taking so long? They are indexed too! In future, I have to run lot of LIKE commands, so this is taking too much time. What has gone wrong?
119956
How can I move MySQL tables from one physical server to another?  Such as this exact scenario:  I have a MySQL server that uses innodb table and is about 20GB size.  I want to move it to a new server, what's the most efficient way to do this?
85275
I have two tables with date and id fields. I want to join on both fields.  I tried   JOIN t2 ON CONCAT(t1.id, t1.date)=CONCAT(t2.id, t2.date)   that works, but it is very slow. is there a better way to do this?
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
81924
I'm trying to find the best way for a basic security system for a website.  I know I want users and groups.  I thought I'd have:  user_table user_id user_name ...  group_type group_id group_name parent_id ...  group_table id user_id group_id   First one is the user, second one is the group, and the third one is the intermediary table that connects the two.  One user has many groups.  Does this sound ok?
30257
I have a table:  table votes (     id,     user,     email,     address,     primary key(id), );   Now I want to make the columns user, email, address unique (together).  How do I do this in MySql?  Of course the example is just... an example. So please don't worry about the semantics.
30707
I'm using MySQL's AUTO_INCREMENT field and InnoDB to support transactions. I noticed when I rollback the transaction, the AUTO_INCREMENT field is not rollbacked? I found out that it was designed this way but are there any workarounds to this?
10493
Do you know a good source to learn how to design SQL solutions?  Beyond the basic language syntax, I'm looking for something to help me understand:   What tables to build and how to link them How to design for different scales (small client APP to a huge distributed website) How to write effective / efficient  / elegant SQL queries
74528
I find this comes up a lot, and I'm not sure the best way to approach it.   The question I have is how to make the decision between using foreign keys to lookup tables, or using lookup table values directly in the tables requesting it, avoiding the lookup table relationship completely.  Points to keep in mind:   With the second method you would need to do mass updates to all records referencing the data if it is changed in the lookup table. This is focused more towards tables that have a lot of the column's referencing many lookup tables.Therefore lots of foreign keys means a lot of joins every time you query the table. This data would be coming from drop down lists which would be pulled from the lookup tables. In order to match up data when reloading, the values need to be in the existing list (related to the first point).   Is there a best practice here, or any key points to consider?
36699
What exactly does null do performance and storage (space) wise in MySQL?  For example:  TINYINT: 1 Byte TINYINT w/NULL 1 byte + somehow stores NULL?
4465
Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables.   My argument, as detailed , is that the performance difference between INT and VARCHAR is negligible, since every INT foreign key reference will require a JOIN to make sense of the reference, a VARCHAR key will directly present the information.  So, does anyone have experience with this particular use-case and the performance concerns associated with it?
71558
I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this...  A great answer was given to building a composite key from multiple foreign keys on  but I want to do this from multiple tables. I am using Oracle 11g.  foreign key (Name, BoughtFrom, TimeBought)    references the_other_table_name (Name, BoughtFrom, TimeBought)   Imagine that the "references" line actually includes multiple tables. I'm not even sure that the above syntax will work with Oracle.  I have one table "Student" and one table "Parent". It is a many-to-many relationship. My association table is named "Relation" and I would like to make a composite primary key from "PAR_ID" and "STU_ID". Both are foreign keys.