… expressions I guess this one is obvious – expressions or functions will block index usage for order by. Sort by column in leading… | Using index | +—-+————-+——-+——-+—————+———+———+——-+——+————-+ 2 rows in set (0.01 sec) The difference between these cases is “i” is primary key while “k” is simply indexes column. Note: In some cases even if it is possible to use index to do ORDER…
Post: PBXT benchmarks
April 8, 2007 By 14 Comments
…used only read (SELECT) queries with different typical data access patterns: primary key single row lookup, primary key range lookup, same access types for primary key…the index. This benchmark is so called “micro” benchmark which concentrates on particular simple storage engine functions and we use …
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
…create an InnoDB FT index before declaring the `FTS_DOC_ID` primary key column properly, MySQL …to update the RT index as data changes, using application code. Since RT indexes are in volatile…function to merge an RT index with an on-disk index, so integrating recent changes with the full index…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…keys | key | key_len | ref | rows | Extra | +—-+————-+————+——-+—————+——+———+——+———+————-+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 1310720 | Using where | | 2 | DERIVED | t2 | index | NULL | c1 | 5 | NULL | 2785914 | Using index…
Post: MyISAM mmap feature (5.1)
May 26, 2006 By 10 Comments
… MyISAM does not cache data, only indexes. MyISAM assumes OS cache is good enough and uses pread/pwrite system calls for reading… functions. To approve effectiveness of memory mapping several benchmarks: For benchmarks I used the sysbench tool and tested different workloads: SELECT by PRIMARY KEY…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… are now. [-] No Clustering Index support Unlike Innodb Falcon does not cluster data by Primary Key or any other key. This is good for… (by using its internal hidden primary key) but this functionality is not exported well as it requires you to get rid of primary keys etc. Some…
Post: Recover BLOB fields
July 1, 2010 By 4 Comments
…index called PRIMARY. It must exist even if a user hasn’t defined the primary index. The PRIMARY index pages are identified by 8-bytes number index… PRIMARY KEY (`ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Here COMPACT format is used… back you should utilize UNHEX function: mysql> LOAD DATA INFILE ‘/…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…PRIMARY KEY, uid INT UNSIGNED NOT NULL, update_time DATETIME NOT NULL, …. INDEX `uid` (uid, update_time), INDEX `bar…use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX… Bad data! We hypothesized that the MIN() function was doing some sort of internal …
Post: DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL
June 22, 2012 By 33 Comments
… Table in Percona Server The main function that is responsible for cleaning …the latch protecting the Adaptive Hash Index (AHI) Scan the buffer pool …int(11) not null auto_increment primary key) And during the benchmark, these …the dataset that sysbench will be using entirely in memory. Furthermore I …
Comment: Why MySQL could be slow with large tables ?
September 24, 2008 By 141 Comments
… (where the pair id1, id2 forms the primary key) using INSERT … ON DUPLICATE KEY UPDATE cnt = cnt + 1. Obviously…, improve index maintanance performance, etc.)? Could it, for example, help to increase “key_buffer_size”?…(id1, id2)? Is there maybe built-in functionality to do such splitting? Or does …
