On Friday I gave a presentation on “MySQL Query Patterns, Optimized” for Percona MySQL Webinars. If you missed it, you can still… in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This made the…, I think what you’re referring to is what I covered as the Derived-Table Solution on slides 55-59. Apologies…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… under concurrency, which this benchmark does not cover. I will make a follow-up post … filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For …=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
…) for MySQL. I actually had more material than I covered (by design), but one thing I regret we didn’t cover was Flow… replication feedback mechanism, unlike anything you find in standard async MySQL replication. It is my belief that the lack of understanding… table. A small fc_limit does however keep the certification index smaller in memory. It keeps the certification interval small, which…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… two cases covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries executed… –max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects=1 –oltp…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… RANGE (to_days(…)). However, the ranges are uneven — older partitions cover larger timespans. The last few partitions are one week each…. * A ‘range’ delete would mostly follow above two points. * Secondary indexes are also BTrees, so they need updating (although delayed). Yes… code) here: http://mysql.rjweb.org/doc.php/partitionmaint The third use case for PARTITIONing involves 2D-like indexing. I have not…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… Now lets see if index can be used as covering index if it has some key parts which are prefixes: mysql> explain select k… parts as covering index if you do not touch columns which only have prefixes in the index. Notice “Using Index” in Extra column. mysql> explain… in the query and if it does, covering index can’t be used. Note: MySQL is however smart enough to make sure prefix…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… of one of the nicest features of the newer MySQL optimizer: the Index Condition Pushdown Optimization, or ICP, which we have previously…) and can take advantage of the covering index technique, did actually take more time to execute: mysql> SELECT role_id FROM cast_info…? It seems that whenever the covering index technique is available, this is always preferred over the ICP optimization: mysql> EXPLAIN SELECT role_id…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… primary key for some queries and use key K as covering index for lookup in other direction. For Innodb Table id2 is… prepdefined A: MySQL Server will not automatically define any indexes for you. Hopefully your CMS already comes with reasonable set of indexes, if… more details. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… how MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index… couple of columns index can be simply much smaller than the data which is one of the reason covering indexes help to speed up queries even if data is in memory. If MySQL is only reading index and not…
Post: Explaining Indexes with a Library Metaphor
August 30, 2011 By 1 Comment
… title in the library. Not using an index If you are not using the index cards, you would have to go shelf… on the hard disk when its not using an index. Using an index You are interested in a book by J.R… saved yourself from walking over to the shelves. Using a covering index You are interested to know how many pages a certain…
