…the –alter statement: Altering new table… ALTER TABLE `test`.`_t_new` add column (foo char(2)) Altered `test`.`_t_new` OK. If the syntax of the –alter statement…you find it? (Hint: think about indexes.) … Notice in each SQL statement the clause: FORCE INDEX(`guest_language`) The tool has …
Post: pt-online-schema-change and default values
June 21, 2012 By 1 Comment
… ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the column type. For example this alter table sbtest add column v varchar(100) not null would work even…`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM `sbtest`.`sbtest` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id`
Comment: Concatenating MyISAM files
November 26, 2012 By 5 Comments
… ‘ALTER TABLE x ADD/DROP INDEX‘, which rewrites the whole .MYD. CREATE TABLE z LIKE x; — duplicate the structure of the original table. ALTER TABLE z DROP INDEX PRIMARY, DROP INDEX my_idx, ADD INDEX PRIMARY ( f1, f2, f3, … ) — Define new keys LOCK TABLE x WRITE, z…
Post: Converting Character Sets
March 17, 2009 By 22 Comments
… convert a database (or set of tables) to a target character set and collation. Approach #1: ALTER TABLE `t1` CONVERT TO CHARACTER SET…) Convert the table to the target character set 4) Convert target columns to their original data types 5) Add FULLTEXT indexes back For… they need to be. Consider the following ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN `c1…
Post: Concatenating MyISAM files
November 19, 2012 By 5 Comments
…> alter table test_concat add data varchar(10); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table… – recovering (with sort) MyISAM-table ‘test_concat’ Data records: 3 – Fixing index 1 myisamchk: error: Couldn’t fix table with quick recovery: Found…
Post: ANALYZE: MyISAM vs Innodb
September 3, 2008 By 9 Comments
…. Add to this Innodb stats are less accurate by nature which means you can allow more data change while your index stats… index prefix is unique (245900 is estimate for the row count in the table) It is worth to note if you do ALTER TABLE Innodb, same as MyISAM will internally run analyze as soon as table is rebuilt and values will be more sensible: mysql> alter table…
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
… SELECT * FROM coordinates FORCE INDEX (x_y_col_a) WHERE x BETWEEN 30 and 40; +—-+————-+————-+——-+—————+———–+———+——+——+————-+ | id | select_type | table | type | possible_keys… use for indexes: ALTER TABLE coordinates ADD x_floor INT NOT NULL, ADD y_floor INT NOT NULL, DROP INDEX x_y_col_a, ADD INDEX x_floor… the original query would have full table scanned if I didn’t use a FORCE INDEX hint. Add more data, and if X…
Post: Small things are better
October 8, 2006 By 15 Comments
… tables allow per table backup and recovery to happen faster. With MySQL and blocking ALTER TABLE there is yet another reason to keep tables small… you need to add extra column to 500GB Innodb table. It will probably take long hours or even days for ALTER TABLE to complete… this way as whole indexes will well fit in memory for such small tables. Not to mention splitting 500 tables to several servers…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… | 216162991863 | 7796961 | | Com_admin_commands | 255868807 | 11893 | | Com_alter_db | 0 | 0 | …snip This output …It is a “garbage” query that just adds latency and makes the overall time … Schema, query, and index optimization Analyzing and optimizing a server’s table and index structures, and …
Post: Joining on range? Wrong!
May 17, 2010 By 10 Comments
… rows: 1 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: p type: eq_ref possible_keys: PRIMARY… and using it for filtering instead: ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD INDEX itm_prd_id__and__itm_order…
