June 18, 2013

Post: Ultimate MySQL variable and status reference list

January 8, 2011 By 4 Comments

…auto_increment_incrementblogpercona.commanual auto_increment…alter_db_upgradeblogpercona.commanual Com_alter_eventblogpercona.commanual Com_alter_functionblogpercona.commanual Com_alter_procedureblogpercona.commanual Com_alter_serverblogpercona.commanual Com_alter_tableblogpercona.commanual Com_alter…

Filed Under: Insight for DBAs, MySQL

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any plans to be able…. Improved Auto Increment handling Yet another known Innodb problem is table locks which are taken for statement duration for Inserts with auto-increment column. This…

Filed Under: Events and Announcements Tagged With:

Post: Statement based replication with Stored Functions, Triggers and Events

December 16, 2011 By 4 Comments

… previous steps but in this case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name DISABLE ON SLAVE. LOCAL…-22.1. AUTO INCREMENTAL VALUES In order to have the same auto incremental values on master and slaves the actual used auto incremental value is logged… and we have gaps? Example: Imagine the table has this values on the auto incremental column: 1,2,3,5 Then we execute…

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

… then I create one, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query… get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166 (42000… sort of sequence value for that column, whether as an auto_increment value or via some other means, but the bit that…

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

Comment: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

… using auto_increment functionality This is right. With some > exception however. For example Innodb tables do internal full table > lock if auto_increment is…> becomes managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table > for few hours…

Filed Under: Insight for Developers Tagged With: , ,

Comment: Duplicate indexes and redundant indexes

January 19, 2012 By 38 Comments

…table if exists emp; drop table if exists dept; create table dept ( deptid integer auto_increment primary key, deptname varchar(20) ); create table emp ( empid integer auto_increment… alter table…

Filed Under: Insight for DBAs Tagged With:

Comment: Spreading .ibd files across multiple disks; the optimization that isn't

December 26, 2010 By 23 Comments

… SSD. ALTER TABLE will work correctly, etc. Now consider that there are two sets of tables in the_db, transactional tables and log tables, and… table the_db.trx1(c1 int auto_increment, c2 int, c3 int, primary key(c1))engine=innodb; create table the_db.trx2(c4 int auto_increment, c1 int, key(c1), c5 int) engine=innodb; — c1 is FK to t1 create table the_db.log(log_id int auto_increment…

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

Comment: Sharing an auto_increment value across multiple MySQL tables

October 5, 2010 By 9 Comments

… only N most recent items): create table some_sharded_table( shard_key int, some_child_key bigint auto_increment not null, key(some_child… keys to shards. You can then partition `some_sharded_table` nicely: alter table some_sharded_table partition by hash(shard_key) partitions 16; All…

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

Post: Joining on range? Wrong!

May 17, 2010 By 10 Comments

… directory consisting of three very simple tables: CREATE TABLE `products` ( `prd_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `prd_name` varchar(32) NOT… introducing additional DATE column and using it for filtering instead: ALTER TABLE items_ordered ADD itm_order_date DATE NOT NULL, ADD…

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

Post: Database problems in MySQL/PHP Applications

August 11, 2006 By 13 Comments

… Not using auto_increment functionality This is right. With some exception however. For example Innodb tables do internal full table lock if auto_increment is …data becomes managable, your ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table for few hours …

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