Dear Netters,
I have a 250 million row table:
CREATE TABLE `UUP` (
`session_id` varchar(38) NOT NULL DEFAULT '',
`app` varchar(64) NOT NULL DEFAULT '',
.....
`bytes_in` bigint(20) unsigned NOT NULL DEFAULT '0',
`bytes_out` bigint(20) unsigned NOT NULL DEFAULT '0',
KEY `session_id` (`session_id`),
KEY `dst_port` (`dst_port`),
KEY `app` (`app`),
KEY `uup_session_app_bytes` (`session_id`,`app`,`bytes_in`,`bytes_out`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=50000000 AVG_ROW_LENGTH=90
Data distribution in the table is such that for each session ID there are on average about 5 records, with different apps (app column).
A typical query against the table does this:
select
sum(bytes_in + bytes_out) volume,
app
from
(select session_id from <session_list>) list
,UUP
where uup.session_id = list.session_id
group by app
order by volume DESC, app;
For giggles (since it was taking forever otherwise), I added index uup_session_app_bytes - which provides coverage of all affected columns. Now the explain plan is showing "using index":
id select_type table type possible_keys key key_len ref rows extra
1 SIMPLE list ALL null null null null 920
1 SIMPLE uup ref uup_session_app_bytes uup_session_app_bytes 40
alex.t.id 5 Using index
So now, using 920 (in this example) session_ids, I am hitting a table with an index, on very high selectivity (250 million rows in UUP, 50 million distinct session_ids). All other columns that I need are part of the index. So, it is not even supposed to hit the table. And it is still taking a looong time.
I played with making changes to the query, to get the data out of the table first, and summarize/group after. The net result is still the same - SLOW.
The data set that is retrieved (in this example) is about 3200 rows - nothing, by any database's standards... What can be done to speed it up?
THANK YOU!
--Alex