… table – myisam_data_pointer_size is 6 default plus …InnoDB Version: 10 Row_format: Compact Rows: 2097405 Avg_row_length: 37 Data_length: 78233600 Max_data_length: 0 Index…operator: mysql> select count(*) from cbool where c1NULL; +———-+ | count(*) | +———-+ | 1048576 | +———-+ 1 row…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…INNODB_PURGE_THREADS | 0 | 1 | | INNODB_OPEN_FILES | 300 | 2000 | | INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend | ibdata1:12M:autoextend | | INNODB_PURGE_BATCH_SIZE…to count …index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6… 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…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… single page size for Innodb table, while data pages and pages for different indexes may well have very different compression ratio. As Innodb indexes are… as Innodb user when I found out ALTERing Innodb tables may take a lot of time. Fast Index creation will allow Innodb to build indexes…’m not sure if Heikki will find a way to count exact number of auto increment values needed or if we…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…InnoDB Version: 10 Row_format: Compact Rows: 1638757 Avg_row_length: 61 Data_length: 100253696 Max_data_length: 0 Index…count(i) from sample lock in share mode; +———-+ | count(i) | +———-+ | 1638400 | +———-+ 1 row in set (7.02 sec) Looking at SHOW INNODB…struct(s), heap size 503104 So…
Post: To pack or not to pack - MyISAM Key compression
May 13, 2006 By 4 Comments
…the index size difference between DEFAULT and 1 values for such key. However it I change it to “key” the size…to integer keys. Integer keys still best but for Innodb for example difference is not so large. Let …benchmarks. I’m testing 4 types of queries: select count(*) from t1, t1 t2 where t1.c=t2.c …
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… application and can range from 5% of total size or less to almost 100%. When your go …, COUNT(user_id) AS user_count FROM user_competition_entry GROUP BY competition_id; be slower without an index? A… in some cases for better index usage. Q: Is the b+ tree innodb index a single or double linked …
Comment: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… / index_size, 2) fit_pct FROM (SELECT index_id, COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed, data_size index_size FROM innodb_buffer_pool_pages_index GROUP BY index_id) bp JOIN innodb_sys_indexes…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…are MyISAM related. join_buffer_size Buffer used for joins without indexes and few other cases….MyISAM and MEMORY tables. max_write_lock_count Variable which may save you from …currently implemented only for Innodb. tmp_table_size This variable specifies maximum size implicit temporary table (created…
Post: Beware the Innodb Table Monitor
November 29, 2011 By 2 Comments
… last post, I decided to use the Innodb Table monitor to diagnose an Index count mismatch error a customers found in their mysqld.err log to verify if the problem still existed. The Innodb Table…, appr.key vals 1, leaf pages 1, size pages 1 FIELDS: FOR_NAME ID INDEX: name REF_IND, id 0 13, fields…
