index merge does never work?

[Ariel Scarpinelli]

Hi,

I´m migrating and old but "big" (4.5 millons of records) table system made in Clipper to MySQL.

As both performance and space are important I looked up at the new feature of MySQL 5, index merge, for using more than a key in the same query for the same table.

The problem is that. I can't never get to use it (or unleast it does not show up in EXPLAIN.. meabe a bug???).

I tried with initialy with this query for testing (the table ventascap1992 is about 1.3 millon of records):

SELECT *
FROM ventascap1992
LEFT JOIN calles ON calles.codigo = ventascap1992.calle
LEFT JOIN inmobiliarias ON inmobiliarias.inmobid = ventascap1992.inmobiliaria
WHERE (
(barrio =9) OR (barrio =12) OR (barrio =18)
) AND (
Fecha BETWEEN '1995-5-30' AND '1995-7-30'
).

The table has an index for barrio an other for fecha. Fecha also is part of the primary key but the documentation says that using intersection for range scan on primary key is not available for MyISAM (does any body understands why??????)

Running an explain on that query I got that it was not using any index at all!!!. In that moment fecha was only the initial part of the primary key (the second is an SMALLINT AUTO_INCREMENT). Then I added an index on fecha only. From that moment it started to use the fecha index on all the queries I tried. I even tried with a simple query like SELECT *
FROM ventascap1992 WHERE (barrio = 9) AND (Fecha = '1995-5-30') and it does not use both indexes!!.

I tried also using FORCE INDEX (barrio, fecha) but in that way it uses barrio only.

Am I doing something wrong?. I'm using 5.0.7-beta in XP (installer binaries from the page)

Does worth using MySQL 5 to get this functionality or is better to stay with 4.1 and find another way to get faster queries with conditions on multiple columns WIHTOUT having a multi-column index for every "kind" of query that the program will use or that's exactly what I have to do?.