…queries only. An additional benefit is that SchemaSpy is command-line driven and builds the html after each run, so I find…mysql-connector-java.jar Example Schema CREATE TABLE `parent` ( `parent_id` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… more efficient 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 the…` table by primary key, the result was a query that took half the time. An important conclusion of my presentation today is that sometimes you find…
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
…to do something like this in MySQL 5.5, you will succeed without errors or warnings: mysql> ALTER TABLE test ADD INDEX (col2); Query…PRIMARY KEY (`id`), KEY `col2` (`col2`), KEY `col2_2` (`col2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) In previous versions of MySQL…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
…in slave server is not working because of an error: Last_SQL_Error: Error ‘Duplicate entry ’4′ for key ‘PRIMARY” on query. Default database: ‘test’. Query…to start working with this new feature in MySQL 5.6. Here are the slides from that session. I hope you find it useful: MySQL…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries … boxes. You can find setup/config details at the very end of this post. …table_open_cache=5000 query_cache_type=OFF performance_schema=0 warmup To warmup server and load data and indices to…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non-PARTITIONed. When PARTITIONed, first it has to find…table“: * If the DELETE is a single row based on a UNIQUE/PRIMARY key and it can prune, the delete involves (1) find…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…tables but because the access type to the tables is “const” MySQL does not count it as access to two tables. In case of “real” access to…of rows examined divided by number of tables in join (B) – if B is less or equals to A your query…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…of foreign key relationships in any complex database. We can find all foreign keys by querying the INFORMATION_SCHEMA. The KEY_COLUMN_USAGE contains information about both primary keys…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…to analyze the information on INDEX_STATISTICS table. mysql> SELECT * FROM INDEX_STATISTICS; +————–+————+————–+———–+ | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | ROWS_READ | +————–+————+————–+———–+ | tpcc | orders | PRIMARY…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
…to identify which of those two indexes is the Primary Key. This is our next step. 2- Identifying the Primary Key There are different methods to find…tables in INFORMATION_SCHEMA that can help us to find the different indexes and types. mysql…
