…moving a subtree to a new position in my tree. Right now, I’m reading the nodes …in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL….length+subtree.length+1 FROM TreePaths AS supertree JOIN TreePaths AS subtree WHERE subtree.ancestor = ‘D’ …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…t match completely, the single one doesn’t know what data should live in it. And this …in the same row” instead of “below, in the next row.” So we’re storing all the same data, right…in a complex query with many joins. I have never seen this approach scale well. Use The Best Technologies You Can MySQL…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
…tables or perform complex queries finding relationships between objects. Normalized structure and a lot of joins is right way to design…In MySQL single query runs as single thread (with exeption of MySQL Cluster) and MySQL issues IO requests one by one for query execution, which means if single query…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…single base table. Knowing this, the query result set is treated as a materialized view over a union of all the already joined… then they can all participate in the distributed query processing. There is almost no …resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
… XtraDB Cluster does the right thing, this …%) on the single one. In this case …in set (0.00 sec) percona2 mysql> insert into percona values (0,’percona2′,’daniel’); Query OK, 1 row affected (0.02 sec) percona2 mysql…d56d-11e1-0800-f7c4916d8330, } joined { 7e6d285b-d56d-11e1-0800-2491595e99bb, } left {…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… a query, then choose the query plan that has the lowest cost. The unit of cost for the MySQL query optimizer is a single random 4k data page read. In… information. If the MySQL optimizer were right and each of these had to go to disk, our STRAIGHT_JOIN plan would take more…
Post: Read/Write Splitting with PHP Webinar Questions Followup
January 23, 2013 By 4 Comments
…they would be in single-threaded MySQL replication. Therefore all nodes stay in sync much more reliably…direct queries to the slave(s) that have a copy of the right database. …in North America and Europe. Join Percona and the MySQL community in Santa Clara, April 22-25 2013 for the Percona Live MySQL…
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
…mysql server, so although we are using PXC, the application will see this as a single MySQL server running on localhost. In…mysql> grant process on *.* to ‘clustercheckuser’@'localhost’ identified by ‘clustercheckpassword!’; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…3 tables, each one with a single row inserted into it: SET FOREIGN…JOINs against tables in this database and seeing that indexes aren’t being used in… problem, right? Not exactly…. (root@localhost) [oops4]> set foreign_key_checks=0; Query OK,… of an inconsistency present when MySQL won’t allow us to …
Post: Data mart or data warehouse?
July 15, 2010 By 14 Comments
…in large part because commercial database software supports hash joins, bitmap indexes, table partitioning, parallel query…MySQL, but it is definitely possibly to use MySQL as long as the right tools and techniques are used. There are ways in…
