… a SQL query optimizer and see how easy it is? ;-) Generative grammars like SQL can produce an infinite variety of queries. The query optimizer analyzes queries for…. But the result was that it created a temporary table to count the movies per production year for each kind_id. It was…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE… default SQL mode,…For example: mysql> alter table test add index redundant (col2, id); Query…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`parent_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE…tables with the string NULL instead of the actual SQL NULL value. This is basically a quick sanity check of your schema for…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… option it produced invalid SQL for cases when there was…for the upstream bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on an existing table…query which opened that table. Bug fixed #1127008 (Sergei Glushchenko). Fix for bug #1070856 introduced a regression in Percona Server for…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… option it produced invalid SQL for cases when there was…for the upstream bug #63144. CREATE TABLE or CREATE TABLE IF NOT EXISTS statements on an existing table…query which opened that table. Bug fixed #1127008 (Sergei Glushchenko). Fix for bug #1070856 introduced a regression in Percona Server for…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
…an error: Last_SQL_Error: Error ‘Duplicate entry ’4′ for key ‘PRIMARY” on query. Default database: ‘test’. Query: ‘insert …that transaction. The way to do it is creating a new empty transaction with it the … servers. pt-table-checksum can help you here, which is found in Percona Toolkit for MySQL. …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… script makes it easy to create incrementally refreshable views from SQL statements. It reads one or more “CREATE TABLE db.schema … AS SELECT” and… view with the old contents (using RENAME TABLE) which means that the view remains available for querying, even during refresh. A complete refresh…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…. For example, we have a parent table Foo and a child table Bar: CREATE TABLE Foo (A INT, B INT, PRIMARY KEY (A, B)); CREATE TABLE Bar… create a conventional table with similar structure, and track our database design manually, so we can use the same SQL queries for quality control: mysql> CREATE TABLE…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
…Table Structure Before moving on, let’s take a look at the structure of the tables involved in the benchmark tests. CREATE TABLE…table with a very large table with no ‘indexed where’ conditions on the big table. BNLH takes half the time to complete the query for…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…Query OK, 0 rows affected (0.11 sec) If we then write a SQL query… algorithm can’t be used here for now (assumed undefined algorithm) | …create table params ( view_name varchar(250), param1_val int, connection_id bigint, primary key(connection_id, view_name) ) engine=innodb; Query…
