[EMAIL PROTECTED] wrote:
<snip>
First, THANK YOU VERY MUCH!!! You actually posted the results of SHOW
CREATE TABLE and EXPLAIN. I wish everyone who had a query problem would do
those two very simple things.
Agreed!
Second, let's look again at what your EXPLAIN is telling you. If you
locate the columns "possible_keys" and "key". Both are NULL for both
tables in your query. That means that there are no indexes that could
possibly cover at least one term in your WHERE clause and that none of
them were actually used. The database engine has to perform a full-table
scan on both tables to solve the query.
Exactly.
The column `no_viaje` is used in a few keys but in none of them is it the
left-most term, so those indexes cannot be used to find tables using just
that column. You already have several indexes defined for both tables so I
am concerned about slowing down your INSERT speed with this suggestion
however to get that query to respond, you need to add another index to one
or both tables
ALTER TABLE `trafico_guia` ADD KEY (`no_viaje`, `fecha_guia`);
ALTER TABLE `trafico_viaje` ADD KEY(`no_viaje`);
Either one will speed up your query but adding both will make it fly.
I don't think that's quite right, because I don't think the multi-column index
on (no_viaje, fecha_guia) will help.
First, I'll rewrite the query with an explicit join:
SELECT a.id_unidad,
b.fecha_guia,
b.monto_pago*b.monto_tipo_cambio
FROM trafico_viaje a
JOIN trafico_guia b
ON a.no_viaje=b.no_viaje
WHERE b.fecha_guia BETWEEN '2005/01/01' AND '2005/01/31';
Now, it should be clear that the best way to execute this query is to first
select rows in trafico_guia (table b) with the right fecha_guia values, then
find corresponding rows in trafico_viaje (table a) where a.no_viaje=b.no_viaje.
For fastest execution, we need two things:
1) An index in trafico_guia with fecha_guia as the leftmost part. This will
alllow quickly finding the correct rows in b. A single column index is all
that's needed, but any multi-column index that starts with fecha_guia would do.
2) An index in trafico_viaje with no_viaje as the leftmost part. This will
allow quickly finding matching rows in a for each row found in b. Again, a
single column index is all that's needed, but any multi-column index that starts
with no_viaje would do.
--
Looking carefully at the two table definitions, I'm a little confused by your
key definitions. Three of your primary keys seem to be multi-column indexes on
(id_area, no_tablename). As a result, table trafico_guia has 3 keys with
id_area as the leftmost part, two of which are needed for foreign key
definitions. There is some inefficiency that could be avoided if that
redundancy could be removed.
In particular, as it relates to this query, the primary key for table
trafico_viaje is (id_area, no_viaje), so table trafico_guia also has an index on
(id_area, no_viaje). Your query, however, only uses no_viaje to join the
tables! That at least implies that no_viaje would be sufficient as the primary
key. Is it the case that the combination of id_area and no_viaje is required to
uniquely identify a row in trafico_viaje, or are values of no_viaje unique? If
the latter, then the primary key should be a single column index on no_viaje,
and area_id should be indexed separately. If that's the case for all three
tables, you could add the indexes you need for this query while actually
reducing your total index size.
Even if you need both values in your primary key, you may be better off swapping
the order. The question is, what do you typically have in your WHERE clauses?
The only reason to have id_area first and no_viaje second would be to help
queries which require a single area_id and a *range* of no_viaje values.
Queries which require a fixed value for each would be helped regardless which
column comes first in the index. If the query here is any indication, sometimes
you only need to match no_viaje without regard to area_id. In that case, you
need no_viaje to come first, or a separate index on no_viaje.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
