… is okay because each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first… not use the STRAIGHT_JOIN, the query optimizer reordered the tables. It seemed to prefer an index-scan of 7 rows in the…(episode_id) as an option there? Yes, I think what you’re referring to is what I covered as the Derived-Table…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… innodb_old_blocks_time had an effect on performance of the benchmark….”. I have labeled each query as Q{FLIGHT_NUMBER}.{QUERY_NUMBER}. In general, …, which this benchmark does not cover. I will make a follow-…level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…covered in the first post: single SELECT queries doing PRIMARY KEY lookups (aka QPS sysbench mode); same MySQL queries…So while the difference in results still requires an explanation, I’d like to highlight one… –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + …
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
…cover larger timespans. The last few partitions are one week each. So, even if the user’s ad hoc query…’ delete would mostly follow above two points. * Secondary indexes are also BTrees, so they need updating…. Conclusion: Not much difference. Yes, DROPping an ‘old’ partition is very efficient, much…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… is safe operation, unless index length increases dramatically queries which can use index can also use prefix of the new index are they ? It…. The query runs 30 times faster – not only because it has to scan less rows but also because it is index covering query now…) indexes and in the end decides to use neither rather doing full index scan until it finds a=100. This looks like an…
Post: Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
… structure to reduce the work a query needs to do. The basic data structure of an index hasn’t changed in recent releases… the cost of a query, but ultimately it arrives at similar conclusions as the star system: define an index with all columns in the predicate, add columns to avoid sorting per query, and add further columns to cover the select…
Post: Covering index and prefix indexes
November 23, 2006 By 5 Comments
… index – which can be significantly smaller in size and having index being covering index, which means query can be executed using only data from the index… simply checks if column is used in the query and if it does, covering index can’t be used. Note: MySQL is however… column length, lets say 20, MySQL will refuse to create an index. So in general handling of prefix keys in MySQL is…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… see “index_merge” as the type of query and the type of index merge algorithm in the “Extra” field. Here’s an example of… this table and implicitly appended to each secondary index, our merged indexes are covering for the query, so that’s also good. When we… the index on parent_id if queries for parent_id != 0 could be satisfied in some other way. After all, if an index isn…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… both directions for different queries. This will use fast primary key for some queries and use key K as covering index for lookup in…. If I have an index on vendor_id, order_id but my query is only selecting on vendor, will the index have any impact… to redesign schema and queries to make it work well. Q: how does a index be used having an index on one column and…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
… data (just one column) and can take advantage of the covering index technique, did actually take more time to execute: mysql> SELECT… the “SELECT note” actually slower? It seems that whenever the covering index technique is available, this is always preferred over the ICP… may not consider it an edge case — but I would call it a limitation of the current query planner. However I would…
