… the table structures: mysqldump –no-data orig > /tmp/orig.schema.sql Create the new target database: mysqladmin create orig…in two scripts: mysql -N -B <<'EOF' > discard-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD TABLESPACE;’) AS _ddl FROM INFORMATION_SCHEMA.TABLES WHERE TABLE…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
…as many as…in a suboptimal form. Q: Doesn’t the primary key solution for random selection…in `title` by a secondary index. But the result was that it created a temporary table…in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the first table in…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
… inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT ld.label_id, cai.inventory_id, COUNT(1) as installed_count from client_app…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… Prabhu). Clustercheck script would hang in case the MySQL server on a node is hung. As a consequence clustercheck script would…1158443 (Raghavendra D Prabhu). In case CREATE TABLE AS SELECT statement was running in parallel with the DDL statement on the selected table, in some cases first statement …
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…CREATE TABLE crashes MySQL There is yet another source of information about the table structure – InnoDB dictionary. Let’s review tables…table lists all indexes the table has, secondary as well as the primary. CREATE TABLE `SYS_INDEXES` ( `TABLE… in our example is 1679: mysql> SELECT * FROM SYS_FIELDS…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…in MySQL 5.6, because all SELECT queries in…creating read views. However, the more general descriptors optimization in Percona Server showed fairly close results in our tests. In…mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, 100, 1000 records in the main table. and transactions for … what I need). Tables looks like: CREATE TABLE `sbtest1` ( `hid` int(10…selectivity is not good for fast inserts, but it is suitable for range selects…mysql/mysql.sock user=root skip-grant-tables TokuDB-related options are all defaults, as…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…refreshed. This is not much different from building a table with CREATE TABLE .. AS SELECT. In fact, this is actually part of what …mysql> SELECT -> customer_id as `customer_id`, -> SUM(price * quantity) as `total_price`, -> COUNT(*) as `total_lines` -> FROM demo.orders as o -> JOIN demo.customers as…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… for the transaction to be completed, as you know that the slave is …select * from t1 where i=1 for update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; When using MySQL 5.1 the second CREATE…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… You can find orphaned rows in Bar: SELECT Bar.ID FROM Bar LEFT …mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN_NAME, ‘ AS `’, P.TABLE_SCHEMA, ‘.’, P.TABLE…: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql…
