June 19, 2013

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

…keys to make it happen: CREATE TABLE `parent` ( `parent_id` int(11) NOT NULL, `v` varchar(10) DEFAULT NULL, PRIMARY KEY…key_checks=1; Query OK, 0 rows affected (0.00 sec) mysql> select * from…

Filed Under: Insight for DBAs, MySQL

Post: Finding out largest tables on MySQL Server

February 4, 2008 By 27 Comments

…to Information Schema but I still wanted to post little query I use for the purpose so I can easily find…primary key with Innodb tables. Of course it also could be perfectly fine tables but it is worth to look. Changing the query a bit to…

Filed Under: Insight for DBAs Tagged With:

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…find the three different types of keys explained before. Lets try it: mysql> show create table t; [...] PRIMARY KEY (`i`), KEY `name` (`name`,`i`), KEY `name_2` (`name`), KEY…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… your primary key correctly. Let’s go back to the ad-by-day table.  If you query date ranges most of the time, you should define the primary key… have the clicks column record the total, and the blue_clicks column record only blue clicks; to find out non-blue clicks you subtract one from the…

Filed Under: Insight for Developers Tagged With: , , , ,

Post: Find unused indexes

June 30, 2012 By Leave a Comment

…to check the usage of our index from the slow query log. The concept is easy to understand. pt-index-usage reads the slow query log and execute every query…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: When the subselect runs faster

March 18, 2010 By 9 Comments

…the rows are large and table does not fit in cache well) When you want to run this query mysql first will try to find…

Filed Under: Insight for Developers, MySQL Tagged With: , , , , ,

Post: ORDER BY ... LIMIT Performance Optimization

September 1, 2006 By 100 Comments

…to have sorting column(s) to be in the leading table. If ORDER BY is going by field from the table which is not first in the…primary key while “k” is simply indexes column. Note: In some cases even if it is possible to use index to…

Filed Under: Insight for Developers Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…to me. Why does InnoDB need to add a hidden column (similar to GEN_CLUST_INDEX when you don’t define a PRIMARY KEY…to or removed from the table and which need to be merged back into or removed from the main index. I’m not entirely sure about the…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

…TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY…query become so much slower ? The reason is its plan benefits from Innodb specific feature – index entries being sorted by primary key for each complete key…

Filed Under: Insight for DBAs, MySQL Tagged With: ,

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

… shard-query/run_worker to contain the following: #!/bin/bash while [ 1 ] do ./worker >> /dev/null 2>&1 < /dev/null done; Where to find the images… even primary keys). Here is the complete InnoDB schema from one shard. The schema is duplicated 20 times (but not the ontime_fact data): DROP TABLE IF…

Filed Under: MySQL
« Older Posts
Newer Posts »