… indexes on AGE and STATE columns. The better strategy is often to have combined multi-column index on (AGE,STATE). Lets see why it is the case. MySQL indexes are (with few exceptions) BTREE indexes – this index type is… not know of the hint in MySQL which would allow forcing using index merge when MySQL does not think it should be…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously discussed… post-execution analysis. So why is the “SELECT note” actually slower? It seems that whenever the covering index technique is available, this… is no workaround, using FORCE-like commands or optimizer_switch flags- we can disable ICP, but not “using index“. So, I wouldn…
Post: Building Indexes by Sorting In Innodb (AKA Fast Index Creation)
June 19, 2012 By 2 Comments
… coded buffer for this operation, which means almost any such index build operation has to use excessive sort merge passes significantly… table is using “fast_index_creation=0″ which allows to disable fast index creation in Percona Server and force complete table to be…. You may be wondering why in this case table rebuild is so close in performance to building index by sort with small…
Comment: COUNT(*) for Innodb Tables
February 16, 2009 By 32 Comments
… time it takes to “select count($field) from $table use index($field);” ($field is the primary key (int autoincrement) in $table… count without use index takes around 8 seconds – the count with forced index takes below 0.1 seconds. any thoughts on why this is…
Comment: InnoDB: look after fragmentation
November 9, 2009 By 12 Comments
Vadim in this case / with that special purpose in mind why do you compare the runtimes of different queries? Q1= SELECT…:00′ At first glance one would use identical queries and FORCE INDEX usage, wouldn’t one?
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… already) 6353 InnoDB: Page may be an index page where index id is 0 25556 InnoDB: (index “PRIMARY” of table “test”.”test”) InnoDB… may think why do not you simply rebuild table by using OPTIMIZE TABLE ? This is because Running in innodb_force_recovery mode…) > 0 || (level == 0 && page_get_page_no(page) == dict_index_get_page(index)) InnoDB: We intentionally generate a memory trap. InnoDB: Submit…
Post: A case for MariaDB's Hash Joins
May 31, 2012 By Leave a Comment
… | 1 | SIMPLE | supplier | index | PRIMARY | i_s_nationkey | 5 | NULL | 20174 | 100.00 | Using index | | 1 | SIMPLE … and in-memory workload. Why, because Hash Join implies scanning… one. So we can force the join algorithm used by…
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… expect: all the dimension columns are indexed on every table, and there’s a separate index on every column in the WHERE… do index lookups in the two dimension tables. MySQL doesn’t want to choose this join order, so we’ll force it… its lack of knowledge about the OS cache. This is why an expert is sometimes needed to provide the missing information…
Post: Falcon Storage Engine Design Review
January 12, 2007 By 19 Comments
… do quite frequently with MySQL 
[+] Compact Indexes Compact Indexes are great. Huge indexes is where large portion of …support for SELECT FOR UPDATE. This is also why Falcon has Optimistic locking concurrency which can … may end up in trouble or be forced to rewrite them to commit often. [+] Blob …
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
…by primary key. This forces the storage engine to… aged out. Digging into why innodb_old_blocks_time change …| pct_buffer_pool | +——————-+——————+——-+——+——+———+—————–+ | `ssb`.`lineorder` | GEN_CLUST_INDEX | 6001 | 2095 | 3906 | 964974 | 73.2544 | |…
