… point. So MySQL Will not use indexes in all cases when it is technically possible. For multiple key part indexes MySQL will only be able to use multiple keyparts if first keyparts matched with “=”. Here is example: mysql> explain… give a hand to MySQL Optimizer and change the query so it can handle it well…. use UNION: mysql> SELECT name FROM people…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… the performance of queries against a MySQL database by distributing the work over multiple machines and/or multiple cores. This is similar to…: BETWEEN or IN clauses, subqueries in the FROM clause, UNION or UNION ALL clauses. If none of those features are used, then… IN. It is also possible to spread your data over multiple machines, scaling out to improve query response times significantly. These…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
… so you do not have to use the union. So changing query to: mysql> SELECT sql_no_cache name FROM people WHERE… have multiple ages to deal with ether as BETWEEN range or as IN filesort appears and query becomes very slow: mysql> explain… UNION to avoid filesort of full table: mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION…
Post: The Optimization That (Often) Isn't: Index Merge Intersection
December 14, 2012 By 6 Comments
… that had single-column indexes on them, MySQL could sometimes make use of the multiple indexes. For instance, “SELECT foo FROM bar WHERE indexed_colA = X OR indexed_colB = Y” might use the index merge union…_colA and indexed_colB and then do a set-theoretic union of the two result sets. Replace that “OR” with an…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…-out and you risk dropping necessary indexes. To accomplish merging multiple result sets, I gathered statistics from both their master and…. 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, INDEX_NAME from master_index_stats ) UNION DISTINCT (select…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…99.9% of the work, due to the multiple levels of result set reduction. Finally, Shard-Query… BETWEEN, IN, subqueries in the FROM clause, and UNION operations to operate fully in parallel. Distributed set…resource which speaks SQL, but right now only MySQL storage nodes are supported. Amdahl’s law …
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… previous post there were questions raised about Index Merge on Multiple Indexes vs Two Column Index efficiency. I mentioned in most… run once I ran them multiple times and measured time appropriately. To remove the overhead of starting MySQL etc from equation I… also worth to watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do full table…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
…”Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number…for subselects, inline views, parts of the UNION. This is also common missunderstanding. Works on … caching systems can effectively use memory on multiple systems so there is no duplication. Memcached…
Post: SQL Injection Questions Followup
July 26, 2012 By 2 Comments
… array of values in your application. Of course there are multiple ways of doing that. Here’s an example of how… MySQL offers, and also it moves too much computation load onto their database server. Their MySQL server became CPU-bound, while their multiple… legitimate name of a Mr. Order, who lives at 123 Union Street? If you are careful to escape or parameterize to…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
…+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D’, 0; The above is the …new node. When you insert a subtree, you’re inserting multiple nodes, and you want to create new paths for … same table in a single query in MySQL. But we can use MySQL‘s multi-table DELETE syntax, to find …
