… UNION to implement loose index scan. First I should mention double IN also works same way so you do not have to use the union. So changing query to: mysql> SELECT sql_no… careful however with these nested IN clauses. MySQL has to internally build all possible combinations for row retrieval which ma become very…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
Post: How adding another table to JOIN can improve performance ?
August 1, 2008 By 12 Comments
… comes from the issue with MySQL optimizer stopping using further index key parts as soon as … ? Performing such query MySQL has to build (and do lookups) for all combinations which is 31*10=… this case you do not even need to trick around equity propagation to make it work) mysql> explain select sum…
Post: Sphinx: Going Beyond full text search
July 23, 2007 By 18 Comments
…do not only want to match links directed to “mysql.com” but links to “www.mysql.com” or “dev.mysql…index, while we do not need them in this case as we’re doing kind of “boolean full text search”. Plus we can make index…
Post: Slow Query Log analyzes tools
September 6, 2006 By 84 Comments
…most time to execute combined. There is a tool mysqldumpslow in MySQL distribution which kind …indexes often help SELECT queries but slow down INSERT/UPDATE ones. Final Note: You do not have to have patched MySQL for these utilities to work. they are …
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…combined together for efficient storage: CREATE TABLE `bbool` ( `b1` bit(1) NOT NULL, `b2` bit(1) NOT NULL, `b3` bit(1) NOT… Max_data_length: 3096224743817215 Index_length: 1024 Data_free:…would not work) or you can use Null-Aware comparison operator: mysql>… this approach ? I do not think so – for …
Post: Is it query which needs to be optimized ?
March 6, 2007 By 9 Comments
… using that schema… which just does not work as a lot of rows needs… solutions, such as summary table. With MySQL 5.0 it is also easy… not be able to use covering index to compute the rows. If the number of combinations… in practice for many applications users do not really care. In some cases you…
Comment: Duplicate indexes and redundant indexes
September 22, 2006 By 38 Comments
…not null combination. It may help also with stats in this case, but not in other case. In general I do not think it is good idea as UNIQUE indexes…not be used on LOAD DATA INFILE/ALTER TABLE for Innodb tables insert buffer would not work…
Post: Are you designing IO bound or CPU bound application ?
December 20, 2006 By 10 Comments
…MySQL Performance Optimization. In fact I probably have to touch it in every second MySQL Consulting work…possibly having performance problems. Also do not look just at “typical” case -…and make sure all selects use index for ORDER BY .. LIMIT. For…than (user_id,sub_id) combined primary key as this one…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… say, “how many is that combined?” you say 20, of course…. no work? Simple, we start thinking in sets like our brains do…. because a mathematical transformation does not have to be decompressed. Third,…index on this table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql…
Post: Sphinx 0.9.8 is released just in time for OSCON 2008
July 20, 2008 By 2 Comments
…0 only when dynamic updates work and starting from 0.9.1 it did not allow too much version flexibility…10-30% faster when it comes to rare word combinations, while if you search for frequent words the …3.6 seconds to do using Sphinx, compared to 7.5 seconds using MySQL with best covering index (so no …
