June 19, 2013

Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact

March 12, 2012 By 12 Comments

… is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5… is, and what is it aimed at. Index Condition Pushdown Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , ,

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

March 4, 2013 By 7 Comments

… table with a single VARCHAR column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_… of the search terms are prefixed with “+” and others have no prefix, results that have the … to 1, dropped/rebuilt the InnoDB index, and tried again. mysql: SELECT id, full_name, MATCH(…

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

Post: Extending Index for Innodb tables can hurt performance in a surprising way

May 22, 2010 By 10 Comments

… is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It… (0.00 sec) The obvious optimization is to extend index from column (a) to column (a,b) right which will make it faster and should not hurt any other queries a lot, right ? mysql…

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

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

…pages for different indexes may well have very different compression ratio. As Innodb indexes are not prefix compressed …for statement duration for Inserts with auto-increment column. This starts giving you problems if you …where already which may come in MySQL 5.2 or even MySQL 5.1. I surely would …

Filed Under: Events and Announcements Tagged With:

Post: Feature Idea: Finding columns which query needs to access

November 17, 2006 By 16 Comments

…columns query needs to access to provide result set as it gives you ideas if you can use covering indexes… It would be great tool for MySQL Performance Optimization if MySQL Server could show this table and …to the columns with table prefix it may take a while to figure our where these columns come …

Filed Under: Insight for DBAs Tagged With:

Post: Performance gotcha of MySQL memory tables

February 1, 2008 By 5 Comments

… the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or… which again comes from the fact MEMORY tables use HASH indexes by default. I’ve created rather similar test table: CREATE….000 rows ALL of them having same value for c column. Now I’m performing random deletes by primary key (DELETE…

Filed Under: Benchmarks, Insight for Developers

Comment: How to find wrong indexing with glance view

August 23, 2008 By 27 Comments

… are ‘AND’, mysql can use either indexes built on multiple columns or intersection of multiple accesses by indexes. When we talk about index_merge_intersection… any of its columns alone. If you don’t have such compound index then mysql most probably use index on “available” column only using ref… be used as prefix for other indexes cuz conditions on these fields are mandatory. 2) You’ve selected mandatory prefix, for example (City…

Filed Under: Insight for DBAs Tagged With:

Post: Be careful when joining on CONCAT

October 16, 2007 By 16 Comments

… on tb1.vid = CONCAT(‘prefix-’, tb2.id) with tb1.vid – indexed varchar(100) and tb2.id – int(11) column. No matter what I… really helps: mysql> EXPLAIN -> SELECT -> tb1.* -> FROM tb2 -> STRAIGHT_JOIN tb1 -> WHERE -> ( -> tb1.vid LIKE ‘prefix-%’ AND -> tb1.vid = CONCAT(‘prefix-’, CAST(tb2…

Filed Under: Insight for Developers Tagged With:

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

… primary key and couple of extra indexes. CREATE TABLE IF NOT EXISTS `$tableName` ( `id…InnoDB ./configure –prefix=/usr/local/mysqltest/mysql- –with-innodb For Falcon ./configure –prefix=/usr/local/mysqltest/mysql- –with… only different the values of accessed column is stored in key. MyISAM and…

Filed Under: Benchmarks Tagged With: ,

Post: To pack or not to pack - MyISAM Key compression

May 13, 2006 By 4 Comments

…index even if it is not packed. Compressed blocks need to be treated differently. For uncompressed index blocks MySQL…key compression for character column type but not for numerical and other column types. Also …hot indexes in memory by packing them it is almost no brainer Summary: Key prefix …

Filed Under: Insight for DBAs Tagged With: , ,
« Older Posts
Newer Posts »