multiple range query, using temporally and filesort

[Beer Bud]

Hi,
I have 3 tables joined by date and hour ranges, the structure of the tables is this

Table: bus_sentido
Create Table: CREATE TABLE `bus_sentido` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `dia` date NOT NULL,
  `hora_inicio` time NOT NULL,
  `hora_fin` time NOT NULL,
  `linea` varchar(4) NOT NULL,
  `sentido` varchar(2) NOT NULL,
  `ppu` varchar(7) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `dia-ppu` (`dia`,`ppu`)
) ENGINE=MyISAM AUTO_INCREMENT=10404 DEFAULT CHARSET=utf8

Table: transaccion
Create Table: CREATE TABLE `transaccion` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `ppu` varchar(7) NOT NULL,
  `dia` date NOT NULL,
  `hora` time NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ppu-dia` (`ppu`,`dia`),
  KEY `dia` (`dia`)
) ENGINE=MyISAM AUTO_INCREMENT=254913 DEFAULT CHARSET=utf8

Table: intervalos
Create Table: CREATE TABLE `intervalos` (
  `id` tinyint(2) unsigned NOT NULL auto_increment,
  `horai` time NOT NULL,
  `horaf` time NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `horas` (`horai`,`horaf`)
) ENGINE=MyISAM AUTO_INCREMENT=49 DEFAULT CHARSET=utf8

the data on the table transaccion is temporally so after is used in the query, and the results is gotten, the table will by truncated, so I have to make the query reliable for the bus_sentido table that will become very big over time.
the table intervalos will have only 48 rows that represent the 30 min of a day

The query is this:

SELECT b.linea, b.sentido,b.dia, ti.id
FROM transaccion t, bus_sentido b, intervalos ti
WHERE (t.ppu=b.ppu and t.dia=b.dia) and (t.hora between b.hora_inicio and b.hora_fin) and (t.hora >= ti.horai and t.hora < ti.horaf)
GROUP BY b.linea, b.sentido, t.dia, ti.id;

explain shows this:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	b 	ALL 	dia-ppu 	NULL 	NULL 	NULL 	8824 	Using temporary; Using filesort
1 	SIMPLE 	t 	ref 	ppu-dia,dia 	ppu-dia 	26 	trans-h.b.ppu,trans-h.b.dia 	439 	Using where
1 	SIMPLE 	ti 	ALL 	horas 	NULL 	NULL 	NULL 	48 	Using where

I realysed that was not using the right index So i changed the query for this:

SELECT b.linea,b.sentido,b.dia,ti.id as media_hora, count(*) as num_trx
FROM (transaccion t force index(dia) left join bus_sentido b on (t.ppu=b.ppu and t.dia=b.dia)) join intervalos ti 
WHERE (t.hora between b.hora_inicio and b.hora_fin and t.hora>=ti.horai and t.hora<ti.horaf)
and b.dia>='2009-04-01' and b.dia<='2009-05-40'
group by b.dia,b.sentido,b.linea,ti.id

and explains shows:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	t 	range 	dia 	dia 	3 	NULL 	254912 	Using where; Using temporary; Using filesort
1 	SIMPLE 	b 	ref 	dia-ppu 	dia-ppu 	26 	trans-h.t.dia,trans-h.t.ppu 	20 	Using where
1 	SIMPLE 	ti 	ALL 	horas 	NULL 	NULL 	NULL 	48 	Using where

But still is too slow, is there anything I can do to make it faster?? even if this mean to change the schema.

thanks.