… function is applied to the columns participating in the join condition and then a hash table lookup is performed, on the hash table we created… query on MariaDB 5.5 is slow as compared to MySQL 5.6. Next interesting thing are the last two columns of the table… find much of a performance improvement from using Hash Join in MariaDB 5.5 or from Key-ordered Scan for TPCH query #3, in…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…to see MySQL memory consumption plotted. I use “VSZ” columns from “ps” output on Linux. It helps me to…Tables MEMORY tables can take memory. There are implicit MEMORY tables which are allocated for query execution, which size can be controlled by tmp_table…can help us to find the cause in most cases. …
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
…” section in MySQL manual but could not find a reason why MySQL does not use index in the following scenario: CREATE TABLE table1 ( id…), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has 512000 randomly generated rows. This query uses index (type: index, key: a, key… 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: 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 gives you ideas if you … 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…
Post: Multi-Column IN clause - Unexpected MySQL Issue
April 4, 2008 By 20 Comments
…column IN makes MySQL to pick doing full table scan in this case, even though the cardinality on the first column…query using more standard single column IN clause: mysql> explain SELECT url FROM 106pages.106pages WHERE url_crc IN (2752937066,3799762538) AND url in…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… tables, and if something is blocked, find its thread ID. Match this up with the ID in SHOW PROCESSLIST, and look in the Host column…). I also like to use the –timeline feature in mk-query-digest, which prints out a sequence of queries in an easy-to-view format, rather… that run in loops and update data, unless you make them commit between iterations. And watch your server to find idle-in-transaction connections…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted to post little query I use for the purpose so I can easily find it later, plus it is quite handy in a… to see number of rows in millions and data and index size in GB so I can save on counting zeros. The last column…
Post: How to find wrong indexing with glance view
August 21, 2008 By 27 Comments
… is not to understand how indexing works and so index all columns used in the queries…. separately. So you end up with table which has say 20 indexes but all single column ones. This… view. If you have queries with multiple column restrictions in WHERE clause you most likely will need to have multiple column indexes for optimal performance…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… TABLE Bar (ID INT PRIMARY KEY, X INT, Y INT, FOREIGN KEY (X,Y) REFERENCES Foo(A,B)); You can find orphaned….Foo columns A,B. Generating SQL with SQL Now that we know the information in KEY_COLUMN_USAGE, we can write a query to fetch every distinct KEY_COLUMN_USAGE.CONSTRAINT_NAME where the REFERENCED_* columns are non…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 30 Comments
… 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… it gets from the single table. I have reverted table to have i1 and i2 as independent columns in this case to look at more typical…
