June 18, 2013

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… ALTER TABLE performance with fast index creation and without it with different buffer pool sizes. Results are pretty interesting. I used modified Sysbench table…(rand()),’-',sha1(rand()),’-',sha1(rand())); and added key on column C: alter table sbtest add key c(c); The box I’m using for…’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_creation…

Filed Under: Insight for DBAs, MySQL

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD COLUMN v VARCHAR(1); Query OK, 4194304 rows affected (1 min…> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE t ADD KEY (c), ADD KEY(c); Query… the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing foreign keys as if expand_fast_index_creation is OFF to…

Filed Under: Benchmarks, Insight for DBAs, MySQL

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

May 22, 2010 By 10 Comments

…column (a,b) right which will make it faster and should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK, … also had another query which previously ran so fast it hardly could be noticed. It however …

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

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

February 26, 2013 By 1 Comment

… InnoDB FTS blazingly-fast, but simply to …table to add column FTS_DOC_ID | +———+——+————————————————–+ This doesn’t make a lot of sense to me. Why does InnoDB need to add…mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT…

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

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…doesn’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-master…to get the totals you add them. Did this gain us anything? We dropped one column, and we just… fast. The star schema is essentially “I admit defeat and accept table scans as a fact of life.” Table…

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

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

…managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table > … to create indexes. Foreign key references may add processing time to inserts, updates, and …wide columns will take up tremendous amounts of memory and disk. Because indexes are fast …

Filed Under: Insight for Developers Tagged With: , ,

Comment: Four ways to optimize paginated displays

November 18, 2010 By 27 Comments

… for the just calculated column “rank” and narrow down the result with $offset and $limit: # create a temporary table with all elements… CREATE TEMPORARY TABLE _tmp (KEY SORT(rank DESC)) SELECT id, rank FROM table; # add a primary key for a fast lookup ALTER TABLE _tmp ADD OFFSET INT…

Filed Under: Insight for Developers Tagged With:

Post: Picking datatype for STATUS fields

August 9, 2008 By 24 Comments

…ENUM – This is what VARCHAR(N) columns get converted during the process of… new values using traditional way (ALTER TABLE) causes table rebuild which is not acceptable… string values. So make sure to add values specify strings for ENUM … which has very small footprint, fast for queries and has no …

Filed Under: Insight for Developers Tagged With:

Comment: To UUID or not to UUID ?

January 20, 2011 By 28 Comments

…key. Why can’t you simply add a Server_Id column to each table? CREATE TABLE `cust` ( `Cust_Id` int(10) unsigned…app that alters the default value of the Server_Id of each table. (You probably also want a ServerId table …Cust_Id=1234 and Server_Id=21. It is fast and it is readable. Of course it all …

Filed Under: Insight for Developers Tagged With: