…index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index…index lookup (for non-index-only columns) involves, reading an index record, and then using the PK column value in the index…not actually mean that queries with MRR are …
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 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: 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: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… the index but not exactly what it gets from the index – typically (unless it is covering index) MySQL gets…In this case the combined index is useless and MySQL has an option of doing full table … column indexes are required for index merge to work and combined indexes can’t be used for such queries. …
Post: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
…second one? That doesn’t make any sense! The index covers x,y and col_a and should be ….00 sec) Fantastic! The only remaining problem with this query is that it’s not quite identical to … WHERE conditions (we are just no longer using an index on them): EXPLAIN SELECT * FROM coordinates WHERE x_…
Comment: Getting around optimizer limitations with an IN() list
January 9, 2010 By 9 Comments
Mark – The queries above work with InnoDB tables. It seems a shame to …, only MyISAM supports indexing of them. You could index an individual point, but you wouldn’t be able to have an index covering the point and the col_a, as I needed in this example (results in an error: ERROR 1210…
Comment: Duplicate indexes and redundant indexes
September 22, 2006 By 38 Comments
…index, because the query still needs an extra lookup to the data? (time that would not have been wasted if the longer index would’ve been a covering index?) I think that is an intersting case too,…. BTW: I really think your idea of an index with a unique prefix is interesting. I …
Comment: ORDER BY ... LIMIT Performance Optimization
February 17, 2011 By 93 Comments
… try and cover an index on each scenario… I’d end up with 20 indexes, and I fear that any update/insert/delete queries to… table; probably more). `country`, `state`, `city` are always in the query. `visible` is either a 1 or 0. 1 meaning the… when there are so many possible scenarios? When I do an ORDER BY `price` DESC, it can take 4-5 seconds…
Comment: COUNT(*) vs COUNT(col)
April 11, 2007 By 41 Comments
…more complex. The points Peter makes about use of indexes, covering indexes and NOT NULL are true, and apply to … to know the accurate number of rows in an entire table at any time. What is the … you need to have the proper indexes to ensure good performance of queries that use a WHERE clause. Further…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… extensive than this article can cover, and might wander into Apache, networking …Com_admin_commands | 255868807 | 11893 | Why is something running an admin command constantly? This is …query, and index optimization Analyzing and optimizing a server’s table and index structures, and the queries…
