…fields. SYS_FIELDS lists fields of all indexes, including the primary. CREATE TABLE `SYS_FIELDS` ( `INDEX_ID` bigint(20) unsigned NOT NULL, `POS` int(10) unsigned NOT NULL… CHARSET=latin1 Fields names explain their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…SELECT * FROM TBL WHERE hash=crc32(‘string’) AND string=’string’ The other thing you need to consider is string comparison in MySQL… CREATE TABLE LINK (id1 int unsigned not null ,id2 int unsigned not null, PRIMARY KEY(id1,id2), KEY …primary key when you do not need an index on a field which is used to…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…had this surprising outcome: mysql> SELECT * FROM cast_info WHERE …NOT NULL AUTO_INCREMENT, `person_id` int(11) NOT NULL, `movie_id` int(11) NOT NULL, `person_role_id` int(11) DEFAULT NULL…field note. The original query had a stable execution time of 1.09 sec, while the following one, which selects…
Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?
January 24, 2008 By 30 Comments
…see a huge tables with varchar/char fields, with small sets of possible values. These are… int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT NULL, city varchar(255) NOT NULL, PRIMARY KEY (id),…performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from …
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
… DEFAULT CHARSET=latin1 Please note field SPACE. For table actor it is equal to 15: mysql> select * from `INNODB_SYS_TABLES` …mysql>CREATE TABLE actor ( actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…NULL, `Month` tinyint(4) DEFAULT NULL, `DayofMonth` tinyint(4) DEFAULT NULL, `DayOfWeek` tinyint(4) DEFAULT NULL, `FlightDate` date NOT NULL, `date_id` smallint(6) NOT NULL…field is selected in the list of columns to show (click show/hide to change the columns) That the “public dns” field is selected…
Post: Data compression in InnoDB for text and blob fields
May 30, 2012 By 2 Comments
… `t1` WHERE `id` < 1000 compare this to: SELECT AVG(LENGTH(COMPRESS(`colTextField`))) FROM `t1` WHERE `id…after MySQL 5.0.3 and has a more compact representation for nulls and variable-length fields …fields were not part of the query request due to Barracuda not storing 768 bytes of the blob on field…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… of index merge algorithm in the “Extra” field. Here’s an example of a table …NOT NULL AUTO_INCREMENT, parent_id INT NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 0, user_type TINYINT UNSIGNED NOT NULL…=1 ORDER BY user_id LIMIT 1; mysql> EXPLAIN SELECT user_id FROM users USE INDEX(user…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… NOT NULL, update_time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar` (some_other_columns) …. ) ENGINE=InnoDB; When he ran this query: SELECT… field first (so that it would be treated explicitly as a string) produces the 0024-06-22 result. The documentation for MySQL…
Post: Adventures in archiving
February 11, 2013 By 16 Comments
… VARCHAR(1000) fields per client requirements. First off the schema tables. CREATE TABLE `archive_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dat… you need to be aware that Archive does not have good performance. e.g. mysql> select id from archive_table order by id desc limit 1; +———+ | id | +———+ | 1143883 | +———+ 1 row in set (2.04 sec) mysql> select id…
