MySql | relational database vs non-relational database from a performance perspective
What I want to ask is if we define relationships, one-to-one, one-to-many, etc., it will increase performance over not creating the relationship, but joining the table on the fly, like
select * from employee inner join user on user.user_id = employee.user_id
I know this question has been asked before and most of the answers I said are performance independent of using relationships.
But I've also heard that creating indexes makes the query faster, so it is possible to create indexes on tables on foreign keys without creating relationships. I'm a little confused about the index.
and what if we have a large database like 100+ tables plus many records, would the relationship be important in terms of executing the database query?
im using mysql and php ..
source to share
Foreign keys are mainly used for data integrity.
Indexing improves performance, of course.
As far as performance with or without foreign keys is concerned, when it is said that they improve performance, it is that when you define the foreign key, you are implicitly defining the index. Such an index is automatically created in the link table if it does not exist.
source to share
Links are used to maintain the referential integrity of a database. They do not affect the execution of the select query at all. They reduce the performance of insert, update, and delete queries, but you rarely need a relational database without referential integrity.
Indexes are what makes the "select" query run faster. They also make insert and update requests significantly slower. To learn more about how indexes work, go to use-the-index-luke . This is by far the best site I have found on this topic.
However, databases usually do indexes automatically when you declare a primary key, and some of them (in particular MySql) do indexes automatically even when you define a foreign key. You can read all about why they do it on the above site.
source to share