…used Xtrabackup simply to clone a server (for replication or migration) or migrate an existing schema to a new server. …B <<'EOF' > discard-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB’;…
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 2
May 30, 2013 By 6 Comments
… replication. That will show where GTIDs shine and where improvements are expected….check the number of records from the t table s1> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ Great…219be3a9-c3ae-11e2-b985-0800272864ba:1 or 3d3871d1-c3ae-11e2-b986-0800272864ba:4 …
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… (correlated, non-correlated, derived tables, scalar subqueries) and we … primary key solution for random selection only work when the … compromise, either of performance, or of accuracy of randomness. …from what you specify in the query? The tuple comparison you’re referring to is this example: WHERE…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…from .frm files or take it from some old backup. A new tool sys_parser can recover the table structure from… is a table identifier. We will need the table id to find indexes of the table. mysql> select * from SYS_TABLES WHERE NAME=’sakila/actor’; …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…of transactions to be either empty or very short, which basically implies…select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT QPS test sysbench –num-threads=<1..1024> –test=oltp.lua –oltp_tables_count=8 –oltp-table…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…table, where probably only recently read records will be cached, resulting in more disc reads. 2. If you need to delete records from…Selects‘ and Inserts, its also about what you do with the table…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
…) Full page output (relatively static pages) Full objects (user or cart object built from several queries) Infrequently changing data (configurations, etc) In…->load() with $db->query(“SELECT * FROM memcached.container WHERE id=’key_id’”); id is PK of the container table While there are increases in…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… is from sysbench, and the table looks like: CREATE TABLE sbtest$I…tables with 1bln of rows on this SSD, and projected InnoDB performance on this size will be the same or…where `id` is sequential, and `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… store hierarchical data, such as threaded comments, personnel org charts, or nested bill-of-materials. Sometimes it’s tricky to do… WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… had a query optimization request from one of our customer. The query was very simple like: SELECT * FROM `table` WHERE (col1=’A'||col1=’B') ORDER… query mysql first will try to find each row where col1 is A or B using index. Then its going to order…. Especially in the cases when WHERE clause is not very selective. So I tried this: select * from table where id in (SELECT id FROM `table` WHERE (col1=’A'||col1=’B…
