… NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update… the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find any…. DELETE a FROM TreePaths AS a JOIN TreePaths AS d ON a.descendant = d.descendant LEFT JOIN TreePaths AS x ON x.ancestor…
Post: Review of MySQL 5.6 Defaults Changes
October 8, 2012 By 10 Comments
… great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5 In general there are … deal with simple and complicated queries efficiently automatically. join_buffer_size = 256k The default is now double … a good one though default could just be left alone. Same applies to sync_relay_log and …
Post: Faster MySQL failover with SELECT mirroring
February 1, 2009 By 9 Comments
… that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the… to find the top tables on the active server and left-join those against the tables on the passive server, with both… have to be the tools I used — it could be MySQL Proxy or a TCP sniffer or anything else. But if…
Post: Optimize Your SQL With Percona's Online Query Advisor!
February 15, 2012 By 23 Comments
… the second addition to our online suite of tools for MySQL users. You paste a query, it tells you what’s… that. Find the bug in this query: select * from t1 left join t2 using(id) where t2.created_date < 2012-02-15... account that you might have already created for our online MySQL Configuration Wizard, which helps you choose good settings for your…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… (BNL) Join of MySQL 5.5.24, Batched Key Access (BKA) Join of MySQL 5.6.5 and Block Nested Loop Hash (BNLH) Join of… that MySQL 5.5 beats both BKA of MySQL 5.6 and Hash Join of MariaDB 5.5. In IO bound test MySQL 5… are joining tables with no indexes on the join condition (Full Join). The best performance with Hash Join can be achieved when the left table…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…test to use the mysql server: [root@percona1 ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id …11e1-0800-2491595e99bb,2) memb { 86928728-d56d-11e1-0800-f7c4916d8330, } joined { } left { } partitioned { 7e6d285b-d56d-11e1-0800-2491595e99bb, }) 120724 10:58:09…
Comment: Multi Column indexes vs Index Merge
October 20, 2009 By 31 Comments
… results for a vehicle. Tried (date_time,vehicle_id) and MYSQL won’t use it, (date_time) and its bringing back… 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…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that…; 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…
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… 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_topic_id = ’224′) Source: http://www.easemarry.com/blog/mysql-limit-optimization/ If you found something better, I would be…
Post: MySQL VIEW as performance troublemaker
August 12, 2007 By 57 Comments
… So how would you get number of comments left by the given user ? mysql> select count(*) from comments where user_id=5… a very artificial query which will JOIN 2 views just to see how indexes are used: mysql> explain select uc.cnt+uc2… in which case which are fully populated and “full join” used to to join between them. In this particular case it is…
