Another question about index's

[Daniel Silva]

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.