CODEWITHSUNDEEP
mysqlopencartopencart-3
Faizan Anwer Ali
Faizan Anwer Ali

Reputation: 671

Why OpenCart uses Subquery instead of LEFT JOIN?

Recently I was researching opencart source code I found that are using subqueries instead of LEFT JOIN.

Opencart v3 look it on file admin/model/user/user.php

$query = "SELECT *, (
        SELECT ug.name 
        FROM `" . DB_PREFIX . "user_group` ug 
        WHERE ug.user_group_id = u.user_group_id
    ) AS user_group
    FROM `" . DB_PREFIX . "user` u
    WHERE u.user_id = '" . (int)$user_id . "'";

I would use like this

$query = "SELECT u.*, ug.name AS user_group 
    FROM `" . DB_PREFIX . "user` u 
    LEFT JOIN `" . DB_PREFIX . "user_group` ug ON ug.user_group_id = u.user_group_id
    WHERE u.user_id = '" . (int)$user_id . "'";

My question is, Is there any performance improvement using subquery in Select column? If yes how so? If no then why this major community is using this way? Also I found that they are not using foreign keys, any idea why?

Upvotes: 0

Views: 85

Answers (1)

spencer7593
spencer7593

Reputation: 108400

A correlated subquery can become a performance problem when the outer query is processing a bloatload of rows; because the subquery gets executed for each row.

It looks like this query returns at most one row, because it looks like user_id is a unique key. Since the outer query returns (at most) a single row, the subquery will get executed only one time.

It also looks like user_group_id is a unique key in the user_group table, so that subquery will return at most one row. (In the more general case, if the subquery returns more that one row, we'd get an error. With the LEFT JOIN, we'd get multiple rows returned.)

Q: Is there any performance improvement using subquery in Select column?

A: There won't be a performance penalty with either form. The form with correlated subquery might be faster, but the difference is not significant.

Q: If yes how so? If no then why this major community is using this way?

A: It's valid SQL, it works, and there's no impetus to make a change.

Q: Also I found that they are not using foreign keys, any idea why?

A: There is no requirement to have the RDBMS enforce referential integrity; if the application is handling it, then we can avoid overhead in the database.

Some storage engines (e.g. MyISAM) don't enforce foreign key constraints.

And foreign keys can sometimes interfere with administrative operations, such has emptying and reloading a table.

It's all design decisions; there are multiple ways to skin a cat. (We're just skimming the surface here; diving deeper would be more opinion based, to make arguments for which way to skin a cat is better.)

Upvotes: 1

Related Questions