… decide between an index on filtering (because of a range) and an index on sorting, is there anything wrong with using a Sub-SELECT… BETWEEN … AND … ) ORDER BY last_online DESC LIMIT 20 For me it seems to work, the main SELECT uses the index for sorting… rows compared to about 6s for using the index for filtering or 2-3s for using the index for sorting. Would anybody see a…
Comment: MySQL: Followup on UNION for query optimization, Query profiling
June 17, 2012 By 14 Comments
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
… to undo changes at rollback. Using this approach, support for Fake Changes have been implemented in order to remove the overhead and… clustered index by InnoDB in the absence of a PRIMARY KEY. #851674 (Alexey Kopytov). Backported fix for MySQL bug #53761 (Wrong estimate for RANGE query with compound indexes). #832528 (Alexey Kopytov). Added a new boolean…
Post: What does Using filesort mean in MySQL?
March 5, 2009 By 27 Comments
…the answer here. If anyone gets it wrong from now on, I know they …same thing. First of all, this is Using temporary. Secondly, temporary tables may go … sort can’t be performed from an index, it’s a filesort. It has nothing…Petrunia’s article on How MySQL executes ORDER BY. You can also read about it…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
…INDEX(`guest_language`) WHERE ((`guest_language` > ?) OR (`guest_language` = ? AND `guest_country` > ?) OR (`guest_language` = ? AND `guest_country` = ? AND `score` >= ?)) ORDER…wrong. If something seems suboptimal, illogical, or just plain wrong…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… database behavior in the cases when self tuning gets it wrong. Q10: When Innodb decides to schedule sequential read-ahead, random… an index, in the same order in the foreign key and the referenced key (this same order condition could be relaxed, though). The index is required for fast lookups: it can be used if the columns…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… I wrote an article about using UNION to implement loose index scan. First I should mention double…do not. Using same access type in EXPLAIN makes it very confusing. I also was wrong about … * from people where age=20 order by last_online desc limit 10) ORDER BY last_online desc limit 10; …
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX(uid…obvious” solution: SELECT update_time FROM foo ORDER BY update_time ORDER BY update_time LIMIT 3; And the … values, and now it’s definitely returning the wrong answer. Always range check your…
Post: Air traffic queries in MyISAM and Tokutek (TokuDB)
November 5, 2009 By 26 Comments
… B-Tree indexes cost grows exponentially on datasize (Bradley Kuszmaul from Tokutek will correct me if I am wrong in… GROUP BY carrier) t2 ON (t.Carrier=t2.Carrier) ORDER BY c3 can be rewritten as SELECT carrier,totalflights…)119.5484.64 Q969.0547.67 For reference I used 5.1.36-Tokutek-2.1.0 for both…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…’s primary keys define the physical order rows are stored in. …things that seem “less advanced” than using a database. Sharding and Partitioning…t get fooled into calculating the wrong thing. Be Creative With Table …improve here? Especially assuming that there are indexes other than the primary key, …
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… right, bugs that cause data corruption, crashes, or wrong query results get the highest priority. We are… maybe you have not inserted in the ascending order of the primary key? I suggest that you… table online. Q38: Seems that adaptive hash indexes are not used for primary key lookups or not showed…
