… rows in sorted order. Once this is implemented similar approach could be used for optimizing ORDER BY with IN. This original query (in memory… pre MySQL 5.0 solution – using UNION instead of single query works in this case: mysql> explain (select * from utest where c1=5) union (select…
Post: Possible optimization for sort_merge and UNION ORDER BY LIMIT
September 18, 2007 By 10 Comments
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer… sorted in execution order, not order of time consumption. Here is one query that can solve this partially: SET @query… the query‘s real response time. If there were, I could add in a UNION …
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
…queries to see what exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query…mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL (select * from people where age=19 order…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…ORDER BY with LIMIT in the back end. In general this type of ORDER BY looks like: SELECT ….. WHERE [conditions] ORDER BY [sort… a bit ugly) will be UNION workaround I already wrote about….query log as query which does not use indexes, even if it is quite fast: mysql> explain select * from test order…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… in sorted order. For example when you query AGE=18 with single column BTREE index MySQL…| i1,i2 | 4,4 | NULL | 299364 | Using sort_union(i1,i2); Using where | +—-+————-+———+————-+—————-+——-+———+——+——–+————————————–+ 1 row …
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
…! Yes I often use this too, in MySQL and in Postgres, it is …, etc, and the all-time favourite, union from a huge archive table and…order) or the relevant row – then I query the archive table, but I know that rows which have the ORDER…the results of this query in the temp table – I sort and limit the …
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
… by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will … common killer queries are GROUP BY Queries and Sort Queries – SELECT name,descr FROM titles ORDER BY rank DESC … seen people trying to join all queries in single UNION (with padding to accommodate different …
