…standpoint (even though a bit ugly) will be UNION workaround I already wrote about. So what …indexed) Force index if needed In some cases MySQL Optimizer may prefer to use different index, which has…in slow query log as query which does not use indexes, even if it is quite fast: mysql> explain select…
Post: Database access Optimization in Web Applications.
September 12, 2006 By 10 Comments
…analyzed by the query. For example SELECT COUNT(*) FROM links WHERE domain = ‘mysql.com’; will …several queries, assuming this query would not need to analyze much more rows because it is optimized … seen people trying to join all queries in single UNION (with padding to accommodate different types…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…of those per query. Sorting and grouping and DISTINCT and UNION may/will …optimize and execute queries; mysqld creates internal structures for the query plan, for example, and keeps it around until the query…mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql…
Post: SQL Injection Questions Followup
July 26, 2012 By 2 Comments
… load onto their database server. Their MySQL server became CPU-bound, while their …site is working well if you’ve optimized the majority of the most frequently-… Mr. Order, who lives at 123 Union Street? If you are careful to… again if you use query results in a subsequent SQL query, then you should …
Post: MySQL: Data Storage or Data Processing
November 18, 2007 By 9 Comments
… single node does not take you that far. MySQL concept of Single Query=Single Thread works well when few rows needs to be analyzed which often forces us to do tricky optimizations because if query… jobs such as sorting, unions/instersections have to be performed on the SQL node. May be MySQL Cluster will evolve and data…
Comment: ORDER BY ... LIMIT Performance Optimization
November 1, 2006 By 100 Comments
Dan, You’re right. This is the challenge and there is no solution which works for everything. If b range is small you can use this trick: http://www.mysqlperformanceblog.com/2006/08/14/mysql-followup-on-union-for-query-optimization-query-profiling/
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…) Note MySQL puts i1 and combined into “possible_key” while it has no way to use them for this query. The query takes 3370ms if this plan is used. Note the query takes about 5 times longer… in memory workload. For UNION case the optimizer is more advanced and it is able to deal with ranges: mysql [localhost] {msandbox} (test…
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… – using IN list instead of range or UNION which however require rather serious application changes …of groups at the same time ? Performing such query MySQL has to build (and do lookups) for …to learn about MySQL Optimizer gotchas 
At the same time I figured out how to make MySQL Optimizer to …
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… overhead of starting MySQL etc from equation I also measured execution of “SELECT 1″ query using same script … index scan. I’m not aware of the optimizer hint which would allow to force index merge … to watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do …
Comment: Using delayed JOIN to optimize count(*) and LIMIT queries
April 16, 2007 By 22 Comments
… in MySQL and in Postgres, it is just one of the tricks of the trade ! I believe the optimizer should…is the way. Recently I had a huge search query (in Postgres) which was horrendously slow. It has … date, item type, etc, and the all-time favourite, union from a huge archive table and an active table…
