…cities_enum ( id int(10) unsigned NOT NULL auto_increment, state enum(‘Alabama’,'Alaska’,'Arizona’,'Arkansas’,'California… important notes about this table before we get to results – this is rather small table… cost still could be significant. And the last test – selecting city and name in arbitrary …
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
… is getting better – here is an example we recently added to our InnoDB course: CREATE TABLE parent ( id INT NOT NULL AUTO_INCREMENT, bogus_column char(32), PRIMARY KEY (id) ) ENGINE=InnoDB; CREATE TABLE child ( id INT NOT NULL AUTO_INCREMENT, parent_id… without the foreign key constraints does not block on the last statement. We also see the expected output change to…
Post: PHP Large result sets and summary tables.
July 6, 2007 By 8 Comments
… you use buffered query the only real call you can get errors is when you run mysql_query , mysql_fetch_row…_unbuffered_query data comes in portions so you can well get an error while fetching rows. If you do not check… table with auto_increment column and fetch data from this table using auto increment column ranges instead. I would especially recommend this last way for…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…big three (Google, Yahoo, MSN) provides real-time reporting last time I was involved with them (and I …key as (day, ad). Don’t use an auto-increment primary key, and don’t put ad first.Â… Any inconsistencies in the atomic data tend to get magnified as it gets aggregated, because you suddenly have a …
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… this: CREATE TABLE `cast_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `person_id` int(11) NOT NULL, `movie_id` int(11… (`id`), KEY `role_id_note` (`role_id`,`note`) ) ENGINE=InnoDB AUTO_INCREMENT=22187769 DEFAULT CHARSET=utf8; The table had 22 million rows… MySQL 5.6 release, and get advantage of the latest integrated tools that MySQL provides with its last GA release? Then I…
Comment: To UUID or not to UUID ?
January 20, 2011 By 28 Comments
…) unsigned NOT NULL AUTO_INCREMENT, `Server_Id` smallint(6) NOT NULL DEFAULT ’1′, `First_Name` char(15) DEFAULT NULL, `Last_Name` char(20) DEFAULT NULL, PRIMARY KEY (`Cust_Id`,`Server_Id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1… row set to the same value). When the table data gets merged from other servers, you still have unique rows and…
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
…message_id` (`from_message_id`,`link_id`) ) ENGINE=InnoDB AUTO_INCREMENT=26141165 DEFAULT CHARSET=utf8; And size of file … 10 14:41 link_out104.ibd So to get statistics we run: xtrabackup –stats –tables=art.link* …, but should be released quite soon. And the last point of the post – if you are badly…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
…in Innodb in 5.0 series. Also note the “Auto Increment Issue” is finally solved in 5.1 which … that cause data corruption, crashes, or wrong query results get the highest priority. We are slowly working on … HT: Since OPTIMIZE rebuilds the whole table, it may last way too long for your 150 GB table! …
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…ICE schema was discussed in the comments of the last post. ICE does not have any indexes (not…_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: NULL Create_time: 2011-05-10 04:26:…-length=16K #we value throughput over response time, get a good plan optimizer-prune-level=0 partition…
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
… MySQL server. If you try to very often you’ll get an error in the log: InnoDB: Error: tablespace id is…_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL… UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (actor_id), KEY idx_actor_last_name (last_name) )ENGINE=InnoDB DEFAULT CHARSET=utf8; This command will…
