I ran across this while trying to perform a similar task with a query containing about a dozen columns. Let us take an example of the right join. c) RIGHT JOIN: Right Join gets all the rows from the Right table and common rows of both tables. I do believe my approach is a bit easier to follow. Syntax: SELECT FROM TABLEA A LEFT JOIN TABLEB B ON A. That is still significantly slower then the other two queries. Adding a key to the user_id on the posts and pages tables avoids the file sort and sped up the slow query to only take 18 seconds. Using EXPLAIN with each of the queries shows that both of your approaches involves a filesort which is avoided with my query. Your updated simpler method took over 2000 times as long (nearly 3 minutes compared to. Limited testing showed nearly identical performance with this query to your query using left join to select subqueries. To test performance differences, I loaded the tables with 16,000 posts and nearly 25,000 pages. You can tell number of rows in each table and whether Id in each table is Clustered Index or not. If inserted table contain less rows like 5,10 or even 20 then it will perform ok. (select count(*) from pages where er_id=er_id) as page_count If you want to write same logic in UDF or Procedure then in place of inserted table you can use main table name like tbl1, tblM. (select count(*) from posts where er_id=er_id) as post_count, My solution involves the use of dependent subqueries. INSERT INTO users (name) VALUES ( 'Jen ') ĬREATE TABLE posts (post_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT) ĬREATE TABLE pages (page_id INT PRIMARY KEY AUTO_INCREMENT, user_id INT)
INSERT INTO users (name) VALUES ( 'Simon ') INSERT INTO users (name) VALUES ( 'Matt ')
CREATE TABLE users (user_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR( 20))
0 kommentar(er)
