… maintained in separate tables and there also exists a users_groups table to define the relationship between them. When someone joins a group… deadlock error. This is not implemented for full BEGIN … COMMIT multi-statement transactions since it cannot be assumed that those are… Example 2, above, how above moving the ‘joined‘ column to the users_groups table so we don’t need to update the…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 141 Comments
… (ie old and rarely accessed data stored in different servers), multi-server partitioning to use combined memory and a lot of… 1% or rows or less full table scan may be faster. Avoid joins to large tables Joining of large data sets using nested loops is very expensive. Try to avoid it. Joins to smaller tables is…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… an SQL database, including the design I call Closure Table. In Closure Table, we store every path in a tree, not only… SELECT from the same table in a single query in MySQL. But we can use MySQL’s multi-table DELETE syntax, to find….descendant, supertree.length+subtree.length+1 FROM TreePaths AS supertree JOIN TreePaths AS subtree WHERE subtree.ancestor = ‘D’ AND supertree.descendant…
Comment: Joining many tables in MySQL - optimizer_search_depth
June 4, 2012 By 8 Comments
… be configured to process multi-table joins with acceptable performance (with some limitations – I doubt it would scale if tables grow). But it’s still much more reliable to use explicit join order, just to make…
Comment: Scaling to 256-way the Sun way
November 12, 2008 By 13 Comments
… different than running business transactions with multi-table join queries.CMT platform are the perfect fit for MySQL multi-instance scalability. I dare you…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…optimization actually is and what benefits it brings. Multi Range Read With traditional secondary index lookups, …have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE …. But this works only with joins and specifically with Block Access Join Algorithms. So I am …
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
… it requires you to rewrite as joins queries that access all columns in the original table. It also requires you to change…/modify data, as more tables are involved, and for example you will have to run any multi table update in a transaction. It will also impact both read and modification speed, precisely for the added join complexity…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…. Or perhaps we have some other tables with ASCII identifiers and we’re doing JOINs against tables in this database and seeing that… ascii not null; ERROR 1146 (42S02): Table ‘oops4.dos2′ doesn’t exist (root@localhost) [oops4]> show tables; +—————–+ | Tables_in_oops4 | +—————–+ | dos1 | | dos3 | +—————–+ 2… SMALLINT, but it will allow us to change from a multi-byte character column to a single-byte character column, so…
Post: Thank you for joining us at Percona Live, NYC 2012
October 7, 2012 By 1 Comment
…Technologies have been showcasing their table grouping technology allowing to dramatically increase performance of complicated join queries. MySQL users … features Continuent offers Automated Failover and Disaster Recovery, Multi Master and Parallel replication, filtering, cross database replication….
Post: The Doom of Multiple Storage Engines
May 8, 2010 By 22 Comments
… engines are much more complicated to test. And I believe multi-storage engine use is really not tested very deeply. I… test all kinds of combination such as running multi-table update joining PBXT and Innodb tables. Operations Things like backup and monitoring becomes a… up flexibility completely, for example one can imagine having Innodb tables which do not log the changes, hence being faster for…
