如何优化左连接查询速度？基于R与MySQL的多表统计问题

Let's break this down step by step to fix your slow query and answer all your questions!

Your original query has a couple of problems that are hurting both correctness and performance:

• Wrong grouping column: You wrote group by p.id, but your SELECT uses p.id_project—this is a typo! You need to group by p.id_project, p.owner_id (since you're selecting both fields) to avoid errors in strict SQL modes and ensure correct grouping per project.
• Unnecessary JOIN with users: You don't need to join the users table at all! The projects table already has an owner_id field, so you can directly filter projects where owner_id is in your target list. This removes an extra join step that's wasting resources.

Here's the corrected, cleaner base query:

SELECT p.id_project, p.owner_id, COUNT(f.user_id) AS follower_count
FROM projects p
LEFT JOIN followers f ON p.id_project = f.repo_id
WHERE p.owner_id IN (123, 526, 852)
GROUP BY p.id_project, p.owner_id;

The biggest win for speed will come from adding targeted indexes—these let MySQL skip full table scans and find data instantly:

1. Composite index on projects: Create an index on (owner_id, id_project)—this lets MySQL quickly find all projects for your target users, and since it includes id_project, it can use that field directly for joining to followers without extra lookups.
2. Index on followers: Create an index on repo_id—this speeds up the COUNT operation by letting MySQL group and count followers per project without scanning the entire followers table.

Run these once in MySQL to create the indexes:

-- Index for fast lookup of user's projects
CREATE INDEX idx_projects_owner_id ON projects(owner_id, id_project);

-- Index for fast follower count per project
CREATE INDEX idx_followers_repo_id ON followers(repo_id);

If you prefer splitting the query into two steps, here's how to do it cleanly in R and merge the results:

Step 1: Fetch the Target Users' Projects #

First, get all projects owned by your specified users:

# Define your target user IDs
target_users <- c(123, 526, 852)

# Query to get their projects
projects_query <- paste0(
  "SELECT id_project, owner_id 
   FROM projects 
   WHERE owner_id IN (", paste(target_users, collapse = ","), ")"
)
projects_df <- dbGetQuery(mydb, projects_query)

Step 2: Fetch Follower Counts for These Projects #

Next, calculate the follower count only for the projects you just fetched:

# Extract project IDs to filter the followers query
project_ids <- paste(projects_df$id_project, collapse = ",")

# Query to get follower counts
followers_query <- paste0(
  "SELECT repo_id, COUNT(user_id) AS follower_count 
   FROM followers 
   WHERE repo_id IN (", project_ids, ") 
   GROUP BY repo_id"
)
followers_df <- dbGetQuery(mydb, followers_query)

Step 3: Merge the Data Frames in R #

Use a left join to keep all projects (even those with zero followers) and fill in the counts. You can use either dplyr (more readable) or base R:

# Option 1: Using dplyr (recommended for readability)
library(dplyr)
final_results <- projects_df %>%
  left_join(followers_df, by = c("id_project" = "repo_id")) %>%
  # Replace NA with 0 for projects with no followers
  mutate(follower_count = ifelse(is.na(follower_count), 0, follower_count))

# Option 2: Using base R
final_results_base <- merge(
  projects_df, 
  followers_df, 
  by.x = "id_project", 
  by.y = "repo_id", 
  all.x = TRUE
)
final_results_base$follower_count[is.na(final_results_base$follower_count)] <- 0

• The split approach reduces the amount of data MySQL has to process at once: first fetching a small list of projects, then only counting followers for those specific projects.
• Indexes eliminate full table scans, which is the biggest cause of slow queries for large datasets.

内容的提问来源于stack exchange，提问作者Thaciana G O Cerqueira