Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Inner joining big table

zanzizanzi EntrantCurrent User Role Beginner
Is there a difference if the table being joined is a big table? (compared to the other way around?)

select p.*, f.notes FROM featured f INNER JOIN items p ON f.item_id = p.id WHERE f.type = 0 ORDER BY f.created_at DESC

select p.*, f.notes FROM items p INNER JOIN featured f ON (f.item_id = p.id AND f.type = 0) WHERE p.featured = 1 ORDER BY f.created_at DESC

select p.*, f.notes FROM items p INNER JOIN featured f ON (f.id = p.featured) WHERE p.featured != 0 ORDER BY f.created_at DESC

Which of these queries would be the best? Is the WHERE clause only executed after the INNER JOIN has been done?

Sorry for these "noob" questions )

Comments

  • PeterPeter Percona CEO Percona Moderator Role
    For Inner join it does not matter. I would probably use conceptual schema to split ON/WHERE clause - ie put foreign key in ON and extra filters in WHERE.

    For LEFT JOIN it is different as as ON clause generates NULL rows in joined table if it is not matches, while WHERE clause works as normally filtering out rows which do not match it.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.