…closure table hierarchy to store regions in a database using MySQL and I’m wondering how can I create a … I know the level of each branch (with the length column) but they are different for each and can be …article with the concatenate path of each entry but the order is not what I need to put it in …
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… on IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size=4M…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
… lookups. This means MySQL can employ many other optimizations like for example if columns other then the secondary key columns are required to… a secondary key on the common join column, let’s suppose col1, then with Key-ordered scan, the secondary key on col1….5 is slow as compared to MySQL 5.6. Next interesting thing are the last two columns of the table above and…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
I looked at the “ORDER BY OPTIMIZATION” section in MySQL manual but could not find a reason why MySQL does not use index… just see this note: With EXPLAIN SELECT … ORDER BY, you can check whether MySQL can use indexes to resolve the query. It cannot if you see Using filesort in the Extra column.
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…NAME, ‘.’, K.COLUMN_NAME) ORDER BY K.ORDINAL_POSITION), ‘) = (‘, GROUP_CONCAT(CONCAT(K.REFERENCED_TABLE_NAME, ‘.’, K.REFERENCED_COLUMN_NAME) ORDER BY K….quality control: mysql> CREATE TABLE test.MY_KEY_COLUMN_USAGE LIKE INFORMATION_SCHEMA.KEY_COLUMN_USAGE; mysql> INSERT INTO test.MY_KEY_COLUMN_USAGE …
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it’s generally applicable…
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… simply indexes column. Note: In some cases even if it is possible to use index to do ORDER BY with JOIN MySQL still…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… for row lookup. Very common problem I see is multi column indexes which are used but only to their short prefix… ORDER BY A ; ORDER BY A,B ; ORDER BY A DESC, B DESC will be able to use full index for sorting (note MySQL may not select to use index for sort if you sort full table without a limit). However ORDER BY B or ORDER…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
… ‘tee’ command. $ cat one.ini [default] user=mysql db=ontime_one password= port=5029 column=date_id mapper=hash gearman=127.0… improved significantly: $ cat shards.ini [default] user=mysql db=ontime password= port=5029 column=date_id mapper=hash gearman=127.0.0…) WHERE Year BETWEEN 1980 and 2011 GROUP BY dest.CityName ORDER BY 2 DESC; Finally, Shard-Query performance continues to improve…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… column `mview$pk` which is an auto_increment BIGINT surrogate key for the table. For complete refresh views which are ordered with ORDER… useful, simply ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as… functions. Now I delete some line items from orders: mysql> delete -> from order_lines -> where order_id -> between 1 and 100 -> limit 500; Query…
