June 18, 2013

Post: Using CHAR keys for joins, how much is the overhead ?

June 18, 2007 By 18 Comments

… not really change beside key lengths: mysql> explain select sum(t1.i+t2.j+length(t2.c)+t1.j…uses key compression for varchar columns so random key lookups become significantly slower. I tried to set pack_keys=… are likely to be different as longer indexes expected to have much worse cache fit, …

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

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…index_creation on. Here and in later examples I’m extending the VARCHAR…Avg_row_length: 29 Data_length: 125452288 Max_data_length: 0 Index_length: 278839296 …index_creation is OFF to avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: MySQL Indexing Best Practices: Webinar Questions Followup

August 16, 2012 By 4 Comments

… queries. This will use fast primary key for some queries and use key K as covering index for lookup in other direction. For… about performance impact of extending index is when you increase its length dramatically, for example adding long varchar column. In such cases it indeed might be better to add another index. Q…

Filed Under: Insight for DBAs, MySQL

Post: Multi Column indexes vs Index Merge

September 19, 2009 By 31 Comments

…length(val)) from idxtest ignore index (combined) where i1=50 and i2=50; +—-+————-+———+————-+—————+——-+———+——+——+————————————-+ | id | select_type | table | type | possible_keys | key | key…

Filed Under: Insight for DBAs, MySQL Tagged With:

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

… a VARCHAR – since you need to estimate average length) on count of records. And it still would be quite inaccurate as secondary indexes… NOT NULL default ’0′, PRIMARY KEY (`link_id`), UNIQUE KEY `domain_id_2` (`domain_id`,`link_id`), KEY `domain_id` (`domain_id`,`from… insert into the primary key in order which makes things very predictable, but the inserts into the secondary key index are random – which…

Filed Under: Percona Software Tagged With: , ,

Post: Joining on range? Wrong!

May 17, 2010 By 10 Comments

…NULL AUTO_INCREMENT, `prd_name` varchar(32) NOT NULL, PRIMARY KEY (`prd_id`), KEY `name` (`prd_name`) ) CREATE TABLE `tags`…`) In 3rd row key_len is only 4 bytes, while the full key length is 4 bytes for…you can notice key_len is 8 bytes, so the full index length. It means both index columns will …

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

Comment: Long PRIMARY KEY for Innodb tables

April 10, 2009 By 8 Comments

I have an InnoDB table with a long varchar primary key, and when I do a “select id…” query, the results … 2) the primary keys all start with the same 18 latin1 characters, and maybe the primary key is only indexed to 10 characters… an index‘s prefix length on MySQL 5.1. It does not display in a “show create table” statement nor a “show index…

Filed Under: Insight for DBAs Tagged With: ,

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…varchar(10) DEFAULT NULL, `Div5TotalGTime` varchar(10) DEFAULT NULL, `Div5LongestGTime` varchar(10) DEFAULT NULL, `Div5WheelsOff` varchar(10) DEFAULT NULL, `Div5TailNum` varchar(10) DEFAULT NULL, KEY…6697533 Avg_row_length: 241 Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_…

Filed Under: MySQL

Post: Dynamic row format for MEMORY tables

September 6, 2011 By 5 Comments

…): The key columns must come before any non-key variable-length columns. For performance reasons, the key columns are stored in a fixed-length block…-length column in between them, it had to be stored in the fixed-length format, either wasting space (VARCHAR) or outright impossible (BLOB). Indexes…

Filed Under: MySQL, Percona Software

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

… is a VARCHAR Not Really a VARCHAR?” Thinking that maybe this was a case of GEN_CLUST_INDEX causing me issues… include initials, numbers, or any other string of length less than 3 (the default), I think you… from this rabbit hole. When defining a FULLTEXT KEY, you can use the “WITH PARSER” modifier to…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts