… (ancestor, descendant, length) SELECT t.ancestor, ‘D’, t.length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT ‘D’, ‘D… IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’) AND ancestor NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You… can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL‘s multi-table…
Comment: Using UNION to implement loose index scan in MySQL
August 11, 2006 By 15 Comments
…mysql> explain SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 OR zip IN (12345,12346, 12347); +—-+————-+——–+————-+—————+———+———+——+——-+—————————————-+ | id | select… | 38821 | Using sort_union(age,zip);…
Post: How much memory can MySQL use in the worst case?
February 12, 2009 By 17 Comments
…. The following query creates at least 2 temporary tables: select * from ( select * from ( select 1 as a ) as b ) as c; You can… of those per query. Sorting and grouping and DISTINCT and UNION may/will also create temp tables; the same applies to… my laptop: mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
…for below output is at the end) mysql> source list.sql +————–+————+—————–+—————–+————-+———————–+————————+ | table_schema | table_name …_table_name, b.referenced_column_name UNION SELECT table_schema, table_name, index_name …
Comment: MySQL VIEW as performance troublemaker
November 20, 2007 By 57 Comments
… a UNION select) stored in separate tables, then use a where clause when selecting from the view, is that a case where MySQL would…
Post: SQL Injection Questions Followup
July 26, 2012 By 2 Comments
…placeholders = join(“,”, array_fill(0, count($params), “?”)); $sql = “SELECT * FROM Bugs WHERE bug_id IN ($placeholders)”; $stmt …computation load onto their database server. Their MySQL server became CPU-bound, while their multiple…a Mr. Order, who lives at 123 Union Street? If you are careful to …
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…. What if we leave MySQL no choice but only to use combined index: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from… typical case: mysql [localhost] {msandbox} (test) > explain select avg(length(val)) from idxtest where i1=50 or i2=50; +—-+————-+———+————-+—————-+——-+———+——+——–+———————————+ | id | select_type | table…. For UNION case the optimizer is more advanced and it is able to deal with ranges: mysql [localhost] {msandbox} (test) > explain select avg…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… INSERT_METHOD=LAST UNION=(`a1`,`a2`) | …mysql> select * from a2; Empty set (0.00 sec) mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select…
Comment: How to find wrong indexing with glance view
August 23, 2008 By 27 Comments
…’s always more selective than any of its columns alone. If you don’t have such compound index then mysql most probably…) and (City, Gender, Age, Available). So mysql can choose between two which is more selective. 3) You can even use (City, Gender….com/2006/08/10/using-union-to-implement-loose-index-scan-to-mysql/#comment-1695 2. http://dev.mysql.com/doc/refman/5…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
… an antique version of PARTITION. Were you rebuilding the MERGE UNION every week? That should have been a _very_ cheap way… _week_ at a time. (reformatting for readability): SELECT o1.* FROM ObjectInstances_T o1 , ( SELECT ObjInstance, MAX(UTCTime) AS UTCTime FROM ObjectInstances_T… as the data arrives, then SELECTing against them. The 3rd SELECT may work better in newer versions of MySQL; it seems to do…
