MySQL Forums
Forum List  »  Performance

Index performance
Posted by: Alex Vilner
Date: June 15, 2010 08:30AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Index performance
3321
June 15, 2010 08:30AM
1021
June 16, 2010 09:54AM
1088
June 16, 2010 12:03PM
1200
June 16, 2010 08:47PM
1141
June 20, 2010 02:30PM
968
June 20, 2010 11:05PM
1298
June 21, 2010 07:35AM
937
June 21, 2010 09:21AM
911
June 21, 2010 10:31AM
1099
June 21, 2010 06:43PM
1260
June 21, 2010 06:55PM


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.