…varchar/char fields, with small sets of possible values. These are “state”, “gender”, “status”, “weapon_type”, etc, etc. Frequently we suggest to change such fields to… VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT NULL, city varchar(255)…
Post: PROCEDURE ANALYSE
March 23, 2009 By 9 Comments
… | | | title | varchar(128) | NO | MUL | | | | uid | int(11) | NO | MUL | 0 | | | status | int(11) | NO | MUL | 1 | | | created | int(11) | NO | MUL | 0 | | | changed | int(… if we defined varchar field too wide. Comparing maximum field length to average length we can see if CHAR or VARCHAR type fits…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
…to replace longish (25-30) indexed varchars with an additional bigint column containing the crc64, and change the indexing to…to Table1 to lookup row by primary key when you do not need an index on a field which is used to… user_id (INT), competition_id(INT); The table is only used to record a user_…
Post: Using CHAR keys for joins, how much is the overhead ?
June 18, 2007 By 18 Comments
… INT fields: CREATE TABLE `intjoin` ( `i` int(10) unsigned NOT NULL, `c` char(10) DEFAULT NULL, `j` int(…explain if someone curious, it did not really change beside key lengths: mysql> explain select sum…to read row data from OS Cache. So what if we convert i and j columns to varchar while sticking to…
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
…varchar(10) DEFAULT NULL, `Div5WheelsOff` varchar(10) DEFAULT NULL, `Div5TailNum` varchar(10) DEFAULT NULL ) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1; Last fields…fields in InfoBright that have less 10.000 distinct values. I tried that, and it affected only these queries. Results with changes…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…field is selected in the list of columns to show (click show/hide to change the columns) That the “public dns” field is selected SSH to…
Post: Adventures in archiving
February 11, 2013 By 16 Comments
… data sliced to sit within the VARCHAR(1000) fields per client requirements. First off the schema tables. CREATE TABLE `archive_table` ( `id` int(11… the following: archive_table (The only change here is the Engine) compressed_data (The change here being VARCHAR(1000) -> BLOB for the intentions…
Post: Innodb row size limitation
April 7, 2011 By 12 Comments
…the following table structure: CREATE TABLE example ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, fname TEXT NOT … string with all multi-byte characters into a VARCHAR(500) column, that value will also be chosen… use this fields. If you have a modular data access API, you shouldn’t have to change code in…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 5 Comments
…. This is a valid question because the MEMORY implementation has changed significantly with this patch–for the fixed-length records too… VARCHAR fields agains the fixed row format. For that we modify the default sysbench schema to be the following: CREATE TABLE sbtest (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, c VARCHAR(4096) NOT NULL…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
…INT NOT NULL DEFAULT 0, status TINYINT UNSIGNED NOT NULL DEFAULT 0, user_type TINYINT UNSIGNED NOT NULL DEFAULT 0, username VARCHAR…to go for a permanent solution. Another option is, of course, to change…
