MySQL Forums
Forum List  »  Optimizer & Parser

another problem with index
Posted by: Daniel Silva
Date: July 06, 2011 08:39AM

Hi,
I'm having a litle problem with one index

CREATE TABLE `date` (
`data_Id` int(10) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`data_Id`),
KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `data` (
`data_Id` int(10) unsigned NOT NULL,
`rop` tinyint(3) unsigned NOT NULL,
`data` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`data_Id`,`rop`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

explain
SELECT
straight_join
*
FROM
date re
join data r
using(data_id)
WHERE rop >=1 and rop <=4
and date='2011-07-05';

this returns:
+----+-------------+-------+------+---------------+---------+---------+-----------------------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-----------------------------------+--------+-------------+
| 1 | SIMPLE | re | ref | PRIMARY,date | date | 3 | const | 348038 | |
| 1 | SIMPLE | r | ref | PRIMARY | PRIMARY | 4 | orange_es_stats_3g_rop.re.data_Id | 93 | Using where |
+----+-------------+-------+------+---------------+---------+---------+-----------------------------------+--------+-------------+

I was expecting the 93 to be a 4, because the condition rop >=1 and rop <=4.
Each data_id has a maximum of 96 rops.

I'm missing something?
Thx

Options: ReplyQuote


Subject
Views
Written By
Posted
another problem with index
2331
July 06, 2011 08:39AM
1205
July 07, 2011 09:53PM
1005
July 08, 2011 02:42AM


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.