… temporary tables cause compared to MEMORY tables. To have things comparable I used medium size table and types which can be presented both in MEMORY… not large enough to fit all key blocks from temporary table index, so we get a lot of key writes which kill… memory MEMORY temporary tables can be 10-100 times faster than disk based MyISAM tables Hopefully MySQL will implement support of dynamic rows for MEMORY tables…
Post: How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
… for given table (cnt), how many of them are dirty (dirty), and what is the percentage of index fits in memory (fit_pct) For illustration purposes I’ve created one table with partitions to show… learn you need a certain fit to buffer pool for tables/indexes for reasonable performance, you may try to count it too…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
….000 of rows which is relatively fast for this (in memory) table but will become unacceptable as soon as you get to… 30 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: g type: index possible_keys: PRIMARY key: PRIMARY key…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… of memory. Blue Columns in this case is the default configuration, second is with expand_fast_index_creation=1 which builds table with… with indexing, such as inserting data in random order can become very slow when index does not fit to memory any more. Creating Index by sort is great Creating index by sort can provide…
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: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
…: the employees table does not fit in memory With the full table scan, the query runs in about 4s. With the full index scan…: the employees table fits in memory With the full table scan, the query runs in about 3.3s. With the full index scan, the… if we know that the table will always be in memory, we should add the FORCE INDEX hint to ensure optimal response time…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… to add another index. Q: We have a database that has about 400GB of indexes. The indexes can’t fit in memory anymore. How does this affect performance? A: Typically you do not need all your indexes… creating an index in every table you create? Example: CREATE TABLE user_competition_entry user_id (INT), competition_id(INT); The table is only…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… come up with “Persistent DRAM” memory device, which would offer some small amount, say 512M memory which is battery backed up just… OPTIMIZE the table, but how long will this take? Is there a facility we can run to show any table / index fragmentation, without having to run the OPTIMIZE? HT: Since OPTIMIZE rebuilds the whole table, it may last…
Post: How much memory Innodb Dictionary can take ?
May 6, 2010 By 3 Comments
… depends on amount of tables you have as well as number of fields and indexes. Innodb allocates this memory once table is accessed and… look like: Total memory allocated 1101004800; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 17721976 (17701384…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…tables have “holes” (deleted rows) which can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables… on disk MyISAM table. It does not limit size of temporary table, neither it applies to tables created as TEMPORARY TABLE, even in memory tables.
