In creating a very fast MySQL query over a half million records, should we avoid foreign keys fields?

Answer 1

Forein keys within MySQL are only for the InnoDB Storage Engine. InnoDB rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same.Thus that will add some complexity within your query since it will add an extra check to your optimization.

BUT InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. Meaning that it will not do a table scan hence will be quicker.

In other words, it is good practice to do foreign keys, the result of optimization comes from indexes. You have to do proper design analysis to decide which key requires indexes or not. Another good practice is to ensure normalized tables. Duplication is not very good. I recommend you to draw the relational diagrams first before creating the queries. Spend some time on it then look in the future of how you think your data will be. There are many ways to optimize your database and each way differs on each layout.

Answer 2

I don't see why it would matter. If you have to go through the process of pulling a block of data from the hard disk into the memory, leaving out the foreign key shouldn't matter much.

Most of the delay in disk I/O is seek time, latency time, or rotation delay (hardware constraints).

Answer 3

If anything they will speed up your query, the join is guaranteed at some point and the query will take advantage of that, otherwise it's a hit or miss.