…test > dump_unoptimized.sql $ mysqldump -uroot test –innodb-optimize-keys > dump_optimized.sql $ time mysql -uroot test < dump_unoptimized.sql real 2m52….innodb-optimize-keys ignores the first UNIQUE index on non-nullable columns when the table has no PRIMARY KEY defined, because in this case InnoDB…
Post: Innodb row size limitation
April 7, 2011 By 11 Comments
… TEXT, fdisclaimer TEXT, fcopyright TEXT, fstylesheet TEXT, fterms TEXT, PRIMARY KEY (id) ) Engine=InnoDB; Now you insert some test data into it: mysql… http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/ for further reference). It’s worth mentioning that this limit… have other negative impact, as it prevents you from using SQL operators/functions directly on the individual fields you want to…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 27 Comments
…(mean): 0.082196 2) Results for VARCHAR: select SQL_NO_CACHE city from cities_varchar WHERE state… It is also interesting to note performance of Innodb tables in this case: for VARCHAR it …_type: SIMPLE table: s type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 1 ref: test.c.state_id rows:…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…PRIMARY KEY (`date_id`), KEY `FlightDate` (`FlightDate`), KEY `Year` (`Year`,`Quarter`,`Month`,`DayOfWeek`), KEY `Quarter` (`Quarter`,`Month`,`DayOfWeek`), KEY `Month` (`Month`,`DayOfWeek`), KEY `DayOfWeek` (`DayOfWeek`) ) ENGINE=InnoDB…/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1] …
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… Choose primary keys very carefully, especially with InnoDB tables — don’t use GUIDs. Which brings me to my next point: Use InnoDB Assuming… capacity. What does this have to do with InnoDB? Data clustering. InnoDB‘s primary keys define the physical order rows are stored in… intra-query parallelization, so ETL jobs written to rely on SQL tend to get really bogged down. In contrast, moving…
Post: JOIN Performance & Charsets
October 21, 2008 By 2 Comments
… NOT NULL, `v` varchar(128) NOT NULL, PRIMARY KEY (`id`), KEY (`char_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; t1 has been populated… WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: select sql_no_cache count(`test`.`t1`.`char_id`) AS `COUNT…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…INNODB STATUS is good enough. As for deadlock information we have similar information about last failed foreign key constraint: ———————— LATEST FOREIGN KEY… `PRIMARY…InnoDB 442 mysql tables in use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… NULL, `c2` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`c2`), KEY `c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=2949071 DEFAULT CHARSET=latin1 1 row… rows affected (0.11 sec) If we then write a SQL query to aggregate over the rows of the view and… varchar(250), param1_val int, connection_id bigint, primary key(connection_id, view_name) ) engine=innodb; Query OK, 0 rows affected (0.10…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… properly, prepare can (if used properly) help increase security against sql injection. > For example I have little wrapper which allows to…’t create either a unique key or a column with an auto_increment primary key in a table using InnoDB, the database will create… also seen others try to make InnoDB use a composite UNIQUE key on a table as a primary key taking up far more space…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
…to hoping that eventually MySQL’s InnoDB becomes close to on par with the competition – in…’re using an ORM like SQL Alchemy and want to avoid writing sql directly in application code or …orders( order_id int unsigned not null auto_increment primary key, item_type_id tinyint unsigned not null, locale_…
