… needed by using a sub-query: SELECT DISTINCT(md.id), (SELECT GROUP_CONCAT(categories.category SEPARATOR ‘, ‘) AS category FROM member_categories LEFT JOIN….member_id LEFT JOIN categories ON mc.category_id = categories.id WHERE mc.category_id IN (138) GROUP BY md.name ORDER BY md.isfeatured…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… to use index hint. [USE INDEX FOR ORDER BY (idx_name)] when we are using Joins in the query. For Ex:- Select a….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_sum…
Comment: ORDER BY ... LIMIT Performance Optimization
July 17, 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… FROM forum_posts WHERE post_topic_id = ’224′ ORDER BY post_id ASC LIMIT 6075 , 1) ORDER BY pa.post_id ASC LIMIT 15
Comment: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
July 7, 2008 By 30 Comments
… s LEFT JOIN cities_join c ON (s.id = c.state_id) WHERE s.name=’Minnesota’ LIMIT 10000,5; since your searching by state maybe if you place it at the left mysql would search for that first and then search for the cities with similar state_id, and it will be ordered…
Comment: ORDER BY ... LIMIT Performance Optimization
April 25, 2011 By 100 Comments
… displaying my property query which is displaying properly and ordering the result by following 4 column p.is_move_up DESC,REPLACE… p LEFT JOIN category ON (p.category_id=category.id) WHERE p.active IN(“yes”,”d”) AND p.is_feature >= SYSDATE() ORDER BY p.is_move_up DESC,REPLACE(IFNULL(p.is_feature,”"),”2015-12-31″,”") DESC, p.listing_order DESC,p…
Comment: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… displaying my property query which is displaying properly and ordering the result by following 4 column p.is_move_up DESC,REPLACE… p LEFT JOIN category ON (p.category_id=category.id) WHERE p.active IN(“yes”,”d”) AND p.is_feature >= SYSDATE() ORDER BY p.is_move_up DESC,REPLACE(IFNULL(p.is_feature,””),”2015-12-31″,””) DESC, p.listing_order DESC,p…
Comment: JOIN Performance & Charsets
April 3, 2010 By 2 Comments
I have one problem with join. I need to reduce query time to it optimized level. … score FROM products p LEFT JOIN product_images pi ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1) INNER JOIN product_search ps… (ps.prodname,ps.prodcode,ps.proddesc,ps.prodsearchkeywords) AGAINST (‘gold’)) ORDER BY score DESC LIMIT 20 EXECUTION TIME: 2.5000+ seconds TABLES…
Comment: Even minor upgrades are not always safe
September 6, 2006 By 5 Comments
… cannot believe someone didn’t point out how awful comma joins are, and that the query should actually read: SELECT c….starttimename FROM tblcourse c INNER JOIN tblcourse_category_extra ce USING (courseid) LEFT JOIN tblref_starttime rst USING (starttimeid) ORDER BY c.approveddate DESC So…
Comment: Why MySQL could be slow with large tables ?
May 27, 2009 By 141 Comments
… stat LEFT JOIN iplist AS iplist ON (iplist.id=stat.ip_interval) WHERE stat.dateline>=1243382400 AND dateline<1243466944 GROUP BY code ORDER BY count… CPU, 2GB RAM). EXPLAIN this query shows that it uses JOIN perfectly. id select_type table type possible_keys key key…
Comment: Join performance of MyISAM and Innodb
April 3, 2010 By 6 Comments
… score FROM products p LEFT JOIN product_images pi ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1) INNER JOIN product_search ps… (ps.prodname,ps.prodcode,ps.proddesc,ps.prodsearchkeywords) AGAINST (‘gold’)) ORDER BY score DESC LIMIT 20 EXECUTION TIME: 2.5000+ seconds TABLES…
