…. “Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there’s… inside MySQL and will have the same execution plan. SELECT * FROM A, B WHERE A.ID = B.ID; SELECT * FROM A JOIN… from “comma joins” because a) the ANSI join syntax is more expressive and you’re going to use it anyway for LEFT JOIN, and…
Post: Using delayed JOIN to optimize count(*) and LIMIT queries
April 6, 2007 By 22 Comments
… joined to get query result. If you’re executing count(*) queries for such result sets MySQL will perform the join even if you use LEFT JOIN so it is not needed which slows down things considerably. In similar way MySQL… in set (0.00 sec) mysql> select count(*) from fact where i select count(*) from fact left join dim on val=id where…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… that the enforcement of foreign keys incurs a significant performance overhead.1,2 MySQL allows us to set FOREIGN_KEY_CHECKS=0… that information, we can generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT… AN ORPHAN mysql> SELECT Bar_ibfk_1.ID AS `test.Bar.ID` FROM test.Bar AS Bar_ibfk_1 LEFT OUTER JOIN test…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… time. OK. Lets start with first simple MyISAM table and join query performed on INT fields: CREATE TABLE `intjoin` ( `i` int(10… DEFAULT CHARSET=utf8 mysql> select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on… key lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j) from intjoin t1 left join intjoin t2 on…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 53 Comments
…derived tables MySQL 5.0 will fail and perform very inefficiently in many counts. MySQL has …you get number of comments left by the given user ? mysql> select count(*) from comments …join” used to to join between them. In this particular case it is not that bad because “join cache” is used to perform…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this …the top tables on the active server and left-join those against the tables on the passive server…workload! The insert buffer can also cause terrible performance. There are some subtleties about exactly what’…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
…assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to …AUTO-INC. We have recovered the concurrency and the performance but with a small cost. Queries like INSERT … … a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer join foo on foo….
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
October 19, 2008 By 22 Comments
… delayed JOIN to optimize count(*) and LIMIT queries… If you’re executing count(*) queries for such result sets MySQL will perform the join even if you use LEFT JOIN so it is not needed which slows down things considerably. In similar way MySQL…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… look only at tables which were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',… s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_…all potentially not needed indexes so you can perform proper QA and ensure you really did …
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 3 Comments
… then repeating it for left or right half. For compressed block this is not going to work and MySQL will need to… performance got some 8 times faster and join on string value is almost 3 times faster. With PACK_KEYS=1 we got join by integer key performing almost…
