… way so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE… you can profile queries to see what exactly happens during query execution – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
Post: Using UNION to implement loose index scan in MySQL
August 10, 2006 By 15 Comments
… can give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people WHERE age=18 AND zip IN (12345,12346, 12347) -> UNION… such query with union. I bet it would even be so with age even if it would take some 100 queries in the union…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…. Then I simply created a view of a UNION DISTINCT of those two tables: mysql> create view used_indexes as (select TABLE…_SCHEMA, TABLE_NAME; Now I can query the ‘all_known_index_usage’ and see the union of both of those datasets. This…_name, i.index_name; Now I can query this view to see my indexes: mysql> select * from all_indexes limit 1 \G…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
Prior to version 5.0, MySQL could only use one index per table in a given query without any exceptions; folks that… = X OR indexed_colB = Y” might use the index merge union algorithm, which would *simultaneously* (this is important, as we will…_type) that is the one that MySQL should be looking at, we can rewrite the query like so, with the following result…
Post: How is join_buffer_size allocated?
July 5, 2010 By 9 Comments
… examining MySQL configuration, we quite often want to know how various buffer…– it has to, because there’s no index. If the query joins several tables this way, you’ll get several …<< 30; # 1GB select * from (select 1 union select 1) as x1 join (select 1 union select 1) as x2 join…. That should…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… enable query_cache_wlock_invalidate=ON. Caching full queries only – Meaning it does not work for subselects, inline views, parts of the UNION… query cache. Now lets speak a bit about Query Cache configuration and mainance. MySQL Manual is pretty good on this: Query Cache Query Cache Status Query…
Post: Recall of Percona Server 5.1.47-11.0
June 29, 2010 By 10 Comments
…queries starting with brackets and having space before select such as ( select * from t1 ) Queries starting with brackets are often used with UNION queries…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…; You can have many of those per query. Sorting and grouping and DISTINCT and UNION may/will also create temp tables; the… needed to parse and optimize and execute queries; mysqld creates internal structures for the query plan, for example, and keeps it around… on my laptop: mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock…
Post: SQL Injection Questions Followup
July 26, 2012 By 2 Comments
…too much computation load onto their database server. Their MySQL server became CPU-bound, while their multiple PHP … of a Mr. Order, who lives at 123 Union Street? If you are careful to escape or …must be protected again if you use query results in a subsequent SQL query, then you should be able …
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
….length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The above is the simple… NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update… SELECT from the same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find…
