条件连接返回NULL值的数据库查询性能优化问题

Great question, let's work through this step by step. First, let's fix a obvious typo in your queries: you wrote sg.setting_id but that should be sv.setting_id (since your joined table is aliased as sv). That's probably just a typo when you drafted the question, but it's worth correcting first to avoid errors.

Why your first query is slow (even though it’s correct) #

Your first query is the right approach for getting all 50 settings (with NULLs for unset ones) because you put the sv.user_id = '35' condition in the ON clause of the LEFT JOIN — this ensures you don’t filter out rows from the settings table where there’s no matching settings_values entry.

Looking at the EXPLAIN output:

• The settings table does a full scan (ALL), but with only 49 rows, this is negligible. The real bottleneck is how the database looks up data in settings_values.
• Right now, you only have an index on user_id. When the database runs the join, it has to:
  1. Take each row from settings
  2. Look up all rows in settings_values where user_id = '35' (using the user_id index)
  3. Filter those rows to find the one matching the current setting_id

Even though this only involves 24 rows per lookup, doing this 49 times adds up with frequent queries. The database can’t efficiently locate the exact (user_id, setting_id) pair without a composite index.

Why the second query fails #

When you move sv.user_id = '35' to the WHERE clause, you turn the LEFT JOIN into an implicit INNER JOIN. Here’s why: for rows where there’s no matching settings_values entry, sv.user_id is NULL, and NULL = '35' evaluates to false. Those rows get filtered out, leaving only the 3 settings the user has configured — which is why you lose the NULL results you need.

The fix: Add a composite index #

The simplest and most effective optimization is to create a composite index on settings_values that covers both user_id and setting_id. This lets the database directly look up the exact row for a given user and setting in one step, instead of filtering after the fact.

Run this command to create the index:

CREATE INDEX idx_user_setting ON cmssps_settings_values(user_id, setting_id);

Once this index is in place, re-run your corrected first query:

SELECT s.setting_id, s.setting_name, sv.value, sv.user_id 
FROM cmssps_settings s 
LEFT JOIN cmssps_settings_values sv 
ON s.setting_id = sv.setting_id AND sv.user_id = '35';

Verify the improvement #

After adding the index, run EXPLAIN again. You should see:

• For the sv table, type is ref or eq_ref
• key is idx_user_setting
• rows drops to 1 per lookup (since each (user_id, setting_id) pair is unique, or at least far faster to resolve)

This will keep your queries fast even with frequent calls, while maintaining the correct NULL results for unset settings.

内容的提问来源于stack exchange，提问作者contool