… (0.00 sec) As you can see if given no hint MySQL will prefer to use index on (cat_id,seller_id… second query explain will look scary with estimated million of rows to analyze but we got rid of filesort so MySQL can… these two example queries is about 100 times so it may be quite serious. To fix this issue MySQL would need to…
Comment: The Optimization That (Often) Isn't: Index Merge Intersection
December 15, 2012 By 6 Comments
… that’s not really the issue. The problem is that MySQL doesn’t correlate any of the statistics from one index… is grossly underestimated. If we go back to the original query and the conditions on those columns, we can easily see… cost-based) than my index hint query which has an estimated search space of about 32K. AFAIK, index hints (or multi-column indexes…
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
… ago to allow more precise logging of query execution times into the slow log. Originally MySQL database offered a second time resolution… hints about installing MySQL on your platform. Also have a look at the files in the Docs directory. Thank you for choosing MySQL…. long_query_time=# Log all queries that have taken more than long_query_time microseconds to execute to file. This option is standard MySQL…
Post: Read/Write Splitting with PHP Webinar Questions Followup
January 23, 2013 By 4 Comments
… of queries your application runs. Q: Hello, was there any good experiences with MySQL Proxy (RW-splitting)? A: Most solutions with MySQL Proxy… probably a good reason to redesign my prototype to use hints embedded in SQL comments, instead of instantiating different wrapper classes… that stored $_SESSION in the MySQL database for persistence, but when we measured query load with pt-query-digest, we found that 30…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… table I am getting: mysql> check table test; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> check table test; +———–+——-+———-+———-+ | … example. Another hint – you may want to CHECK your MyISAM table you use for recovery after MySQL crashes to make …
Post: Analyzing air traffic performance with InfoBright and MonetDB
October 2, 2009 By 50 Comments
… MonetDB, let see how fast they are in different queries. First favorite query for any database benchmarker is SELECT count(*) FROM ontime…: do not try EXPLAIN this query in InfoBright. MySQL is really stupid here, and EXPLAIN for this query took 6 min! If you….14s UPDATE (5-Oct-2009): James Birchall recommended to use hint COMMENT ‘lookup’ for fields in InfoBright that have less 10…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
…. Most Simple Way Number One is to simply run two queries: mysql> select grp, count(*) cnt from dt where slack like “a… the result set we want from single query ? Sure. Here is Way Number Three: mysql> select * from (select grp, count(*) cnt from… execution method for GROUP BY by using SQL_BIG_RESULT hint I can see GROUP BY executing about same time as…
Post: Full table scan vs full index scan performance
November 23, 2012 By 15 Comments
… employees tables: mysql> ALTER TABLE employees ADD INDEX idx_first (first_name),ENGINE=InnoDB; And then let’s consider this query: SELECT… should add the FORCE INDEX hint to ensure optimal response time. Now let’s modify the query by selecting only the first_name field instead of selecting all the fields: mysql> explain SELECT first…
Post: When the subselect runs faster
March 18, 2010 By 9 Comments
… fit in cache well) When you want to run this query mysql first will try to find each row where col1 is… return them in the right order. MySQL can chose only one of them to execute the query – use index to find rows… make MySQL check WHERE clause while scanning table in index order. We can just use FORCE INDEX hint to override MySQL index choice: mysql…
Post: Multiple column index vs multiple indexes
August 22, 2008 By 13 Comments
… remove the overhead of starting MySQL etc from equation I also measured execution of “SELECT 1″ query using same script and subtracted… single index scan. I’m not aware of the optimizer hint which would allow to force index merge as you can… of low cardinality even though MySQL can’t use index well. You’re right MySQL can’t and MySQL does not – Full table…
