… `i` (`i`,`j`(5),`k`) ) ENGINE=MyISAM Now lets see if index can be used as covering index if it has some…: const rows: 1 Extra: Using where 1 row in set (0.00 sec) In this case index can’t be used as covering index as… is – we’re using column j in where clause. Even if this particular like check can be done only by using index, MySQL is not…
Post: Multi Column indexes vs Index Merge
September 19, 2009 By 31 Comments
…use AND between such columns in where clause. Index merge does helps performance but it is far from performance of combined index in this case. In…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take a look briefly… limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for…` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… use select/group by/join/where. You may not use sub-queries or any non-deterministic functions like NOW() or RAND(). HAVING clauses, ORDER BY clauses… during refresh. A complete refresh type view can use non-deterministic functions like NOW(). In fact any valid SELECT statement can be materialized…
Post: To SQL_CALC_FOUND_ROWS or not to SQL_CALC_FOUND_ROWS?
August 28, 2007 By 57 Comments
… on this table using indexed column b in where clause: mysql> SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS * FROM count_test WHERE b = 555… now – we need too check how long our COUNT query would take: mysql> SELECT SQL_NO_CACHE count(*) FROM count_test WHERE… seconds in case when two queries used. So, obvious conclusion from this simple test is: when we have appropriate indexes for WHERE/ORDER clause in…
Post: A rule of thumb for choosing column order in indexes
June 5, 2009 By 15 Comments
… a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific…. EXPLAIN says there are no possible_keys. SELECT * FROM tbl WHERE status=’waiting’ AND source=’twitter’ AND no_send_before
Post: Using any general purpose computer as a special purpose SIMD computer
May 16, 2011 By 5 Comments
…. Please use this technology constructively and for peaceful purposes. Love one another and let’s solve all the complex problems in the…)`), `count(*)`=`count(*)` + VALUES(`count(*)`) Now, consider the query with BETWEEN 1 and 6 added to the where clause. This creates boundary conditions for…
Post: Missing Data - rows used to generate result set
July 20, 2008 By 15 Comments
… of rows in it) Now if we have the same table with no indexes and query SELECT GENDER, COUNT(*) FROM PEOPLE WHERE COUNTRY… if you have clause like TITLE LIKE “%MYSQL%” you may instead use Full Text Search indexes. If you have WHERE ID%100=0… WHERE COUNTRY=’USA’ As of MySQL 5.0 MySQL will materialize the subquery in the from clause fully and so “use” all rows in result…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
… can happen? Someone runs UPDATE or DELETE and forgets the where clause or filters weren’t quite right The application had a… mysqlbinlog in 5.6 could be used. With mysqlbinlog 5.6, you can now pull binary logs in real time to another server using “mysqlbinlog … –read-from-remote-server –raw –stop-never” Useful to mirror…
Post: Moving Subtrees in Closure Table Hierarchies
February 14, 2011 By 18 Comments
… NOT IN (SELECT descendant FROM TreePaths WHERE ancestor = ‘D’); But MySQL complains: “You can’t specify target table ‘TreePaths’ for update in FROM clause.” We can’t DELETE and SELECT from the same table in a single query in MySQL. But we can use MySQL… new node. But now we need to insert all the nodes of the subtree. We use a Cartesian join between…
