June 19, 2013

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…, like this: +———————————–+———————-+———————-+ | Variable_name | Value | Value | +———————————–+———————-+———————-+ | Aborted_clients | …running mk-duplicate-key-checker to find redundant indexes, and …

Filed Under: Insight for Developers Tagged With:

Comment: Should you name indexes while doing ALTER TABLE ?

May 29, 2008 By 10 Comments

> What happens if you try to create the same index without specifying index > name? MySQL will happily create (and maintain) as many duplicate keys as you > like without even giving you a warning: No, it won’t. It stops at 64. (Don’t ask me how I know…) – Eric

Filed Under: Insight for DBAs Tagged With:

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

February 26, 2013 By 1 Comment

…_innodb ADD FULLTEXT KEY (full_name, details); Query OK, 0 rows affected, 1 warning (39.73 sec) Records: 0 Duplicates: 0 Warnings: 1…_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000): InnoDB presently supports one FULLTEXT index creation at a…

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

Comment: Innodb vs MySQL index counts

November 30, 2011 By 6 Comments

… 258 len 7; INDEX: name PRIMARY, id 50, fields 1/5, uniq 1, type 3 root page 3, appr.key vals 2, leaf…; Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 > select * from test; +—-+——-+ | id | data | +—-+——-+ | 1 | 49999… 258 len 7; INDEX: name PRIMARY, id 52, fields 1/4, uniq 1, type 3 root page 3, appr.key vals 2, leaf…

Filed Under: MySQL

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

…table ADD INDEX (id); — 5 Tickets Used Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings:…child (id INT, parent_id INT, -> INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id) -> ON …90 max_connections=3000 query_cache_size=0 skip-name-resolve table_cache=…

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

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…OK, 543 rows affected (0.01 sec) Records: 543 Duplicates: 0 Warnings: 0 As far as I can …, dropped/rebuilt the InnoDB index, and tried again. mysql: SELECT id, full_name, MATCH(full_name, details) AGAINST (‘”Thomas…. Watch out! When you add your first FULLTEXT KEY to an InnoDB table, be prepared for a…

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

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…not have any indexes (not even primary keys). Here is the complete InnoDB schema from one shard. The schema is duplicated 20 …mysql> show table status like ‘ontime_fact’G *************************** 1. row *************************** Name: ontime_fact Engine: InnoDB Version: 10 Row_format: Compact …

Filed Under: MySQL

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

…key, fkto3 char(32) not null, fkfrom3 char(32) not null, index(fkto3), index(fkfrom3), foreign key (fkto3) references dos3(fkfrom1), foreign key… row affected (0.09 sec) Records: 1 Duplicates: 0 Warnings: 0 But suppose that … table rather than the temporary table name that was displayed in the error …

Filed Under: Insight for DBAs, MySQL

Post: COUNT(*) vs COUNT(col)

April 10, 2007 By 45 Comments

…` int(11) default NULL, `val2` int(10) unsigned NOT NULL, KEY `i` (`i`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 mysql> select count… table fact drop key i, add key(i,val); Query OK, 7340032 rows affected (37.15 sec) Records: 7340032 Duplicates: 0 Warnings: 0…

Filed Under: Insight for Developers Tagged With: ,

Comment: Duplicate indexes and redundant indexes

August 24, 2006 By 38 Comments

… that many third-party tools have duplicate indexes too. Here is one table from Mambo with a duplicate index on a long column: CREATE… default ’0′, `name` varchar(255) NOT NULL default ”, `hidden` int(11) NOT NULL default ’0′, PRIMARY KEY (`aro_id`), UNIQUE KEY `section_value…_aro` (`section_value`,`value`), UNIQUE KEY `#__gacl_section_value_value_aro` (`section_value`,`value`), KEY `hidden_aro` (`hidden`), KEY `#__gacl_hidden_aro` (`hidden…

Filed Under: Insight for DBAs Tagged With:
« Older Posts
Newer Posts »