have you guys tried indexing state_id on the cities_join table. though I read something about frequent inserts with indexes… also SELECT SQL_NO_CACHE c.city FROM states s LEFT JOIN cities_join c ON (s.id = c.state_id) WHERE s… searching by state maybe if you place it at the left mysql would search for that first and then search for…
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…
Comment: GROUP_CONCAT useful GROUP BY extension
January 17, 2013 By 49 Comments
…`, `p`.`description`, group_concat( `pf`.`filename` SEPARATOR ‘:’ ) FROM `pages` `p` LEFT JOIN `pages_files` `pf` USING( `pages_id` ) will get all pages…_concat( concat( `pf`.`description`, ‘: ‘, `pf`.`filename` ) SEPARATOR ‘;’ ) FROM `pages` `p` LEFT JOIN `pages_files` `pf` USING( `pages_id` ) to get same as…
Comment: Is there a performance difference between JOIN and WHERE?
January 16, 2013 By 30 Comments
Hi there, I am using a LEFT JOIN and i was wondering if having many condition – ie. SELECT * FROM table1 LEFT JOIN table2 ON table2.x=table1.y AND table2.b=table1.a can i add many conditions or rather leaving it to max one? Please help or advise. Thanks a bunch Jerome
Comment: Quickly finding unused indexes (and estimating their size)
December 6, 2012 By 2 Comments
… wish tutorials would make more use of NOT EXISTS vs LEFT JOIN NULL. I believe it is slightly more readable in that… in the other. Also, MySQL is likely to optimize that “left join null” using a “not exists” behind the scenes anyway.
Comment: Why MySQL could be slow with large tables ?
January 7, 2009 By 141 Comments
… split the searchable data into two tables and did a LEFT JOIN to get the results. I noticed that when there were… it with 35 million records with no performance problems. “Avoid LEFT JOIN if possible” is the best tip you could get when…
Post: Unused indexes by single query
September 12, 2008 By 14 Comments
….TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS ON (s.TABLE_SCHEMA = INDXS….TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IST ON CONCAT_WS(‘.’, s.TABLE…
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…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…. DELETE a FROM TreePaths AS a JOIN TreePaths AS d ON a.descendant = d.descendant LEFT JOIN TreePaths AS x ON x.ancestor… the nodes of the subtree. We use a Cartesian join between the ancestors of B (going up) and the descendants….descendant, supertree.length+subtree.length+1 FROM TreePaths AS supertree JOIN TreePaths AS subtree WHERE subtree.ancestor = ‘D’ AND supertree.descendant…
Comment: Is there a performance difference between JOIN and WHERE?
April 17, 2010 By 30 Comments
… programmer by looking at the style of writing joins or queries in general. Comma joins mean that the guy is not as… are times when I need to change a inner join to left join and it is very difficult to explain the change to…
