… accepted as a bug as early as 2005!). The report explains more in detail what the “will be disallowed in a… 5.6, as the indexes are different. But as you may know, we can use the second index not only for filtering on… all applications out there that use MySQL, for which we at Percona tend to find redundant indexes. And second, because in some…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… better than another equivalent solution. If you’re talking about using a temporary table to store an interim result set, and… each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first, by looking up… first, grouping by kind_id in index order. This made the first table in the EXPLAIN seem like it was scanning more…
Comment: Using index for ORDER BY vs restricting number of rows.
April 11, 2013 By 8 Comments
… order by NUM desc limit 5 Follow was index。 the explain: type:index，Extra:Using index; Using temporary; Using filesort i am chinese, so my english is…
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…Fields names explain their content. Index id in our example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS…pages-ibdata1/FIL_PAGE_INDEX/<index_id> 4. Load dumps of the dictionary tables into some MySQL server. Use LOAD DATA INFILE …
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
…-process/node communication). Results: First of all, we used a smaller dataset for this benchmarks round, …of the descriptors optimization that I thought was explained in the original post, but seems to …nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + …
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 8 Comments
… poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site… is possible to use index for further restriction and than using file sort, rather than using index for sorting and doing non-index based filtering for… rows: 296338 Extra: Using where; Using filesort 1 row in set (0.00 sec) mysql> explain select * from goods force index(cat_id) where…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 28 Comments
… MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index to… is only reading index and not accessing rows you will see “using index” in EXPLAIN output. These are the main “core” use for indexes. You can also see others like using index for group by…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…Using intersect(i2,i1); Using where | +—-+————-+———+————-+—————-+——-+———+——+——+————————————-+ 1 row in set (0.00 sec) Hm… Optimizer decides to use index…
Post: MySQL EXPLAIN limits and errors.
July 24, 2006 By 6 Comments
… Extra | +—-+————-+——-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | lt | index | NULL | PRIMARY | 4 | NULL | 1600 | Using index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec) This statement obviously will not scan more than 10 rows but EXPLAIN shows…
Post: When EXPLAIN estimates can go wrong!
October 13, 2011 By 7 Comments
…: 1 ref: const rows: 185440 Extra: Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where type…: 1 ref: NULL rows: 339184 Extra: Using where; Using index On 5.5: mysql [localhost] {msandbox} (foo2) > explain select count(*) from test_estimate where…
