…. I know the level of each branch (with the length column) but they are different for each and can be more… article with the concatenate path of each entry but the order is not what I need to put it in html…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
…_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) For each pk_column…_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in InnoDB contain primary key columns) Buffer each pk_column…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… employ many other optimizations like for example if columns other then the secondary key columns are required to be fetched, then MRR… the PK by arranging the secondary key values by PK column and then performing the lookup, and then there are other… a secondary key on the common join column, let’s suppose col1, then with Key-ordered scan, the secondary key on col1…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
…, key: a, key_len: 103, Extra: ”): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1698, 1 This one uses filesort (Extra: Using filesort): EXPLAIN SELECT * FROM table1 ORDER BY a LIMIT 1699, 1 Can someone EXPLAIN if there…. It cannot if you see Using filesort in the Extra column.
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… order by by last column, in fact index can be used for ORDER BY if sorting is done by leading column(s). Note however columns following column used for order… order by. Sort by column in leading table if you have JOIN with ORDER BY … LIMIT you should try hard to have sorting column…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… traversing ranges between values in sorted order. For example when you query AGE=18 with single column BTREE index MySQL will dive into the index to find first matching row and when will continue scanning index in order… this case. In case you’re using OR between columns – single column indexes are required for index merge to work and combined…
Post: MySQL Performance - eliminating ORDER BY function
October 17, 2007 By 6 Comments
… index on the column even if function is very simple such as arithmetic operation. Same can apply to order by, if you… sorts by function is able to avoid order by while second which uses direct column value needs to do the filesort: mysql> explain select * from tst where i=5 and date(d)=date(now()) order…
Post: Feature Idea: Finding columns which query needs to access
November 17, 2006 By 16 Comments
In query examinations it is often interesting which columns query needs to access to provide result set as it … as GROUP BY and ORDER BY. For complex queries especially if they do not refer to the columns with table prefix it may take a while to figure our where these columns come from and so how they can be optimized.
Post: Using index for ORDER BY vs restricting number of rows.
February 16, 2007 By 7 Comments
… * from goods where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1…(cat_id) where cat_id=5 and seller_id=1 order by price desc limit 10 \G *************************** 1. row *************************** id: 1… this issue MySQL would need to better take into account column selectivity together with LIMIT range. If there are only few…
