… split the searchable data into two tables and did a LEFT JOIN to get the results. I noticed that when there were… and tested it with 35 million records with no performance problems. “Avoid LEFT JOIN if possible” is the best tip you could get…
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
August 23, 2007 By 22 Comments
… acts a little weard:P While this works when performing LEFT JOINS, with INNER JOINS it’s a different thing. Let’s say we… & 10000, like in the above example, but let’s perform an innerj join instead… The first subquery will return the requested 10…
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…
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… passive master, too. Is that all? “Buffer pool warmed up, performance is better, case closed!” No. This isn’t as simple… to find the top tables on the active server and left-join those against the tables on the passive server, with both… write-only workload! The insert buffer can also cause terrible performance. There are some subtleties about exactly what’s happening that…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
….TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz… to drop all potentially not needed indexes so you can perform proper QA and ensure you really did not drop anything…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
… ignoring the quorum, we ask to the cluster to not perform the majority calculation to define the Primary Component (PC). A…-11e1-0800-2491595e99bb,2) memb { 86928728-d56d-11e1-0800-f7c4916d8330, } joined { } left { } partitioned { 7e6d285b-d56d-11e1-0800-2491595e99bb, }) 120724 10:58:09…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… on AUTO-INC. We have recovered the concurrency and the performance but with a small cost. Queries like INSERT … ON DUPLICATE…; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer join foo on foo.name=1… INSERT IGNORE: insert into foo(name) select 1 from mutex left outer join foo on foo.name=1 where mutex.i = 1…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… learn that the enforcement of foreign keys incurs a significant performance overhead.1,2 MySQL allows us to set FOREIGN_KEY… find orphaned rows in Bar: SELECT Bar.ID FROM Bar LEFT OUTER JOIN Foo ON (Bar.X,Bar.Y) = (Foo.A,Foo….TABLE_SCHEMA, ‘.’, K.TABLE_NAME, ‘ AS ‘, K.CONSTRAINT_NAME, ‘ ‘, ‘LEFT OUTER JOIN ‘, K.REFERENCED_TABLE_SCHEMA, ‘.’, K.REFERENCED_TABLE_NAME, ‘ AS ‘, K…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
… the key value and repeating it then repeating it for left or right half. For compressed block this is not going… 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…
Comment: How to find wrong indexing with glance view
August 22, 2008 By 27 Comments
… no one “tests” scenarios based on the comments left here. There are situations which can warrant any number…from my tests it just doesn’t match the performance of indexing an INT, unsigned or not. You can…till I attended a presentation by Jay Pipes on Join Fu. (http://www.jpipes.com/) You think this …
