by Peter Zaitsev | Feb 16, 2007 | Insight for Developers
One interesting problem with MySQL Optimizer I frequently run into is making poor decision when it comes to choosing between using index for ORDER BY or using index for restriction. Consider we’re running web site which sell goods, goods may be from different...
by Peter Zaitsev | Jan 31, 2007 | Insight for DBAs
There is one bug, or “missing feature” in MySQL Optimizer which may give you hard time causing performance problems which may be hard to track down, it is using only part of the index when full index can be used or using shorter index while there is longer...
by Peter Zaitsev | Nov 23, 2006 | Insight for DBAs
I already wrote in the blog as well mentioned in presentation there is often a choice you have to make between having prefix index – which can be significantly smaller in size and having index being covering index, which means query can be executed using only...
by Peter Zaitsev | Oct 30, 2006 | Insight for Developers
I’ve run into the following thread couple of days ago: Basically someone is using sphinx to perform search simply on attributes (date, group etc) and get sorted result set and claiming it is way faster than getting it with MySQL. Honestly I can well believe it...
by Peter Zaitsev | Aug 31, 2006 | Insight for Developers
Starting MySQL 4.1, MySQL had support for what is called derived tables, inline views or basically subselects in the from clause. In MySQL 5.0 support for views was added. These features are quite related to each other but how do they compare in terms of performance ?...
by Peter Zaitsev | Aug 14, 2006 | Insight for Developers
Loose Index Scan with Double IN Few days ago I wrote an article about using 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_cache name FROM...
by Peter Zaitsev | Aug 10, 2006 | Insight for Developers
One little known fact about MySQL Indexing, however very important for successfull MySQL Performance Optimization is understanding when exactly MySQL is going to use index and how it is going to do them. So if you have table people with KEY(age,zip) and you will run...
by Peter Zaitsev | Jul 24, 2006 | Insight for Developers
One nice feature added for EXPLAIN statement in MySQL 4.1 is EXTENDED keyword which provides you with some helpful additional information on query optimization. It should be used together with SHOW WARNINGS to get information about how query looks after transformation...
by Peter Zaitsev | Jul 24, 2006 | Insight for Developers
Running EXPLAIN for problematic queries is very powerful tool for MySQL Performance optimization. If you’ve been using this tool a lot you probably noticed it is not always provide adequate information. Here is list of things you may wish to watch out. EXPLAIN...
by Vadim Tkachenko | Jun 2, 2006 | Insight for DBAs
MySQL does not always make a right decision about indexes usage. Condsider a simple table: CREATE TABLE `t2` ( `ID` int(11) default NULL, `ID1` int(11) default NULL, `SUBNAME` varchar(32) default NULL, KEY `ID1` (`ID1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 123456...
by Peter Zaitsev | May 9, 2006 | Insight for Developers
Comments to my previous posts, especially this one by Gokhan inspired me to write a bit about descending indexes and about loose index scan, or what Gokhan calls “better range” support. None of these are actially related to Innodb tables in general –...
