… about which query patterns the optimizer recognizes, and which are left to the developer to refactor manually. There could also be… compromise, either of performance, or of accuracy of randomness. The ORDER BY RAND() solution is known to have poor performance, but… order of columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On the Dynamic Pivot, the straight join…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…, is joined by the column l_orderkey which is the left-most PK column, so reading the table orders in PK order has another… the left operand which is the table orders are far greater than the size of the join buffer. The size of the join buffer… join is slow in this case, because we have to refill the join buffer with rows from orders table many times, and hash join…
Comment: ORDER BY ... LIMIT Performance Optimization
July 9, 2007 By 100 Comments
… whatever LEFT JOIN categories ON category = categories.id LEFT JOIN users ON torrents.owner = users.id WHERE visible = ‘yes’ AND inverted_timestamp>=’3111748733′ ORDER BY… = ‘yes’ ORDER BY size DESC LIMIT 3800,1 SELECT users.username,bla bla FROM whatever LEFT JOIN categories ON category = categories.id LEFT JOIN users…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…, all_indexes.index_name as index_name from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where…_UNIQUE, count(*) as COLUMN_CNT, group_concat( i.column_name order by SEQ_IN_INDEX ASC SEPARATOR ‘,’) as COLUMN_NAMES, s… / ( 1024 * 1024 ), 2) as index_size_mb from all_indexes left join used_indexes using (TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) where…
Comment: ORDER BY ... LIMIT Performance Optimization
July 16, 2008 By 100 Comments
… forum_posts AS pa LEFT JOIN forum_posts_text AS pb ON pa.post_id = pb.post_id LEFT JOIN forum_users ON user_id = post_poster WHERE post_topic_id = ’450′ ORDER BY pa.post_id ASC LIMIT 5475 , 15 versus SELECT * FROM forum_posts AS pa LEFT JOIN forum_posts_text…
Comment: Multi Column indexes vs Index Merge
October 20, 2009 By 31 Comments
… left join moving_events m on g.id = m.gps_position_id left join overspeed_events o on g.id = o.gps_position_id left join locations l on l.id = gl.location_id left join ios….id is not null or o.id is not null ) order by g.date_time; Thanks, Dan
Comment: GROUP_CONCAT useful GROUP BY extension
May 11, 2009 By 49 Comments
… LEFT JOIN member_categories mc ON md.id = mc.member_id LEFT JOIN categories ON mc.category_id = categories.id GROUP BY md.name ORDER… category FROM member_directory md LEFT JOIN member_categories mc ON md.id = mc.member_id LEFT JOIN categories ON mc.category_id = categories.id WHERE mc.category_id = 187 GROUP BY md.id ORDER BY md…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 8, 2007 By 22 Comments
… FORCE INDEX (DPrivate) LEFT JOIN EntryHitCounters EHC ON (EHC.EntryID=E.EntryID) WHERE E.DiaryID = 11693 AND Private = 0 ORDER BY Date DESC… DiaryID=11693 and Private=0 Order By Date DESC LIMIT 1235,20) D LEFT JOIN Entries E USING (EntryID) LEFT JOIN EntryHitCounters EHC USING (EntryID…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… using Joins in the query. For Ex:- Select a.test_id, b.test_data, c.test_details From test a left join test_data b on a.test_id = b.test_id left join test_details c on b.test_id = c.test_id Where a.test_flag = ‘Y’ Order by c.test…
Comment: GROUP_CONCAT useful GROUP BY extension
May 11, 2009 By 49 Comments
… GROUP_CONCAT(categories.category SEPARATOR ‘, ‘) AS category FROM member_categories LEFT JOIN categories ON member_categories.category_id = categories.id WHERE member…) AS category FROM member_directory md LEFT JOIN member_categories mc ON md.id = mc.member_id LEFT JOIN categories ON mc.category_id = categories.id WHERE mc.category_id IN (138) GROUP BY md.name ORDER BY…
