Another question about index's
Hi
I have two tables:
CREATE TABLE `relation` (
`data_Id` INT unsigned NOT NULL,
`fdn_Id` MEDIUMINT unsigned NOT NULL,
`date` DATE NOT NULL,
PRIMARY KEY (`data_Id`)
)
ENGINE = MYISAM;
CREATE TABLE `cells` (
`data_Id` int unsigned NOT NULL,
`rop` tinyint unsigned NOT NULL,
`value` int unsigned default NULL,
PRIMARY KEY (`data_Id`,`rop`)
) ENGINE=MyISAM;
the first table has 643.179 rows and 2 indexs:
PRIMARY
date (data_Id, date)
the second has 2.754.048 rows.
my problem is that when joining the two tables, a full scan i made in the second :(
explain
SELECT r.date, rr.rop, value
FROM relation r, cells rr
WHERE r.data_Id=rr.data_Id AND r.date='2008-1-4' AND rr.rop>=1 AND rr.rop<=96
or
explain
SELECT r.date, rr.rop, value
FROM relation r, cells rr
WHERE r.data_Id=rr.data_Id AND r.date='2008-1-4'
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------------+
| 1 | SIMPLE | rr | ALL | PRIMARY | NULL | NULL | NULL | 2754048 | Using where |
| 1 | SIMPLE | r | eq_ref | PRIMARY,date | PRIMARY | 4 | db.rr.data_Id | 1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+---------+-------------+
can anyone help me?
thx in advance
Edited 3 time(s). Last edit at 01/08/2008 04:41AM by Daniel Silva.
Subject
Views
Written By
Posted
Another question about index's
3266
January 05, 2008 06:35PM
2236
March 12, 2008 09:55AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.