…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX…, and it has the exact column definition in the same order. In other words, it checks for duplicate keys, but not…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key(i); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t1 add key… the problems comes from the automatic naming – depending on order you add indexes indexes can get different names which makes scripted upgrade…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…(rand()),’-',sha1(rand())); and added key on column C: alter table sbtest add key c(c); The box I’m using for test… world. Working with indexing, such as inserting data in random order can become very slow when index does not fit to…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…User Statistics is an improvement on Percona Server that adds some tables to Information Schema with useful information to …[...] ALTER TABLE `tpcc`.`order_line` DROP KEY `fkey_order_line_2`; — type:non-unique ALTER TABLE `tpcc`.`orders` DROP KEY `idx_orders`; — type:non-unique ALTER TABLE `tpcc…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
… CHARSET=latin1 mysql> select * from a order by id limit 10; +—-+——————————————+ | id | c | +—-+——————————————+ | 1 | 813cf02d7d65de2639014dd1fb574d4c481ecac7 | …(4 min 5.52 sec) mysql> alter table a add key(c); Query OK, 0 rows affected…
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
…: ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD INDEX itm_prd_id__and__itm_order_date (itm_prd_id, itm_order_date); UPDATE items_ordered SET itm_order_date = DATE(itm_order_timestamp…_type: SIMPLE table: i type: ref possible_keys: itm_prd_id__and__itm_order_timestamp,itm_prd_id__and__itm_order_date key…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… employees tables: mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB; And then let’s consider this query: SELECT * FROM employees ORDER…: mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: employees type: ALL possible_keys…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK… where a=100 order by id desc limit 1; +—-+————-+———-+——+—————+——+———+——-+——+——————————————+ | id | select_type | table | type | possible_keys | …
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
…Percona Server however adds number of tables to Information Schema…table_schema = innodb_sys_tables.SCHEMA ORDER BY cnt DESC LIMIT 20; +————–+————–+————–+——+——-+——–+———+ | table_schema | table…affected by batch jobs, alter tables, optimize table etc – the …
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… test order by id; +—-+——+ | id | x | +—-+——+ | 1 | foo | | 2 | bar | +—-+——+ 2 rows in set (0.00 sec) session2 > ALTER TABLE test add column c…) session6 > ALTER TABLE test_2 DROP COLUMN c; session7 > select * from test_2 order by id; session8 > select * from test_2 order by id…
