Does mysql 5.0.x have the ability to use more than one index per table on a select ? We had to rewrite a simple
"select id from table_a where last_name like 'smith%' and first_name like 'john%'"
to
"select id from table_a left join
(
select id from table_a where last_name like 'smith%'
group by id
) as t2
on
t2.id = table_a.id
where
table_a.first_name like 'john%' limit 201;
We had tried an index on last_name, an index on first_name, and a combo index of (last_name, first_name). We cut the run time from 1min 57sec to 3seconds.
walt
Heikki Tuuri wrote:
Hi!
InnoDB is the MySQL table type that supports FOREIGN KEY constraints, row-level locking, Oracle-style consistent, non-locking SELECTs, multiple tablespaces, and a non-free online hot backup tool.
Release 5.0.2 is a snapshot of the 5.0 development branch of MySQL. Unfortunately, this snapshot still contains some critical bugs, like http://bugs.mysql.com/bug.php?id=5401, which causes MySQL to return wrong results if a SELECT uses two indexes at the same time.
InnoDB in MySQL-5.0.2 is almost the same as in the upcoming MySQL-4.1.8 release. Marko's new compact InnoDB table format did not make it to 5.0.2. The new compact table format will be pushed to the 5.0 BitKeeper tree today, and it will be included in 5.0.3. The biggest downside of InnoDB when compared to MyISAM has been that InnoDB tables take a lot more space than MyISAM tables. The new compact InnoDB table format will make InnoDB tables substantially smaller.
You can look at the InnoDB roadmap at http://www.innodb.com/todo.php
InnoDB functionality changed from 4.1:
* If you specify the option innodb_locks_unsafe_for_binlog in my.cnf, InnoDB no longer in an UPDATE or a DELETE locks rows that do not get updated or deleted. This greatly reduces the probability of deadlocks. If you do not specify the option, InnoDB locks all rows that the UPDATE or DELETE scans, to ensure serializability.
Upgrading to 5.0.2:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to MySQL-4.1.4 or later. The storage format in those MySQL versions for a TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then no rebuild of TIMESTAMP tables is needed.
* If you have stored characters < ASCII(32) to non-latin1 non-BINARY indexed columns in MySQL versions <= 4.1.2, then you have to rebuild those tables after you upgrade to >= 4.1.3. The reason is that the sorting order of those characters and the space character changes for some character sets in 4.1.3. See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases where you need to rebuild the table. Also MyISAM tables have to be rebuilt or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes >= 128) in database names, table names, constraint names, or column names in versions < 4.1, you cannot upgrade to >= 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table.
Best regards,
Heikki Tuuri Innobase Oy http://www.innodb.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
