MySQL Forums :: Performance :: Index performance


Advanced Search

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 3203 Alex Vilner 06/15/2010 08:30AM
Re: Index performance 1002 Rick James 06/16/2010 09:54AM
Re: Index performance 1065 Alex Vilner 06/16/2010 12:03PM
Re: Index performance 1179 Rick James 06/16/2010 08:47PM
Re: Index performance 1117 Alex Vilner 06/20/2010 02:30PM
Re: Index performance 946 Rick James 06/20/2010 11:05PM
Re: Index performance 1277 Alex Vilner 06/21/2010 07:35AM
Re: Index performance 919 Rick James 06/21/2010 09:21AM
Re: Index performance 888 Alex Vilner 06/21/2010 10:31AM
Re: Index performance 1080 Rick James 06/21/2010 06:43PM
Re: Index performance 1238 Alex Vilner 06/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.