Slow Query - Table Size or Index Issue?

HI All - fairly new to mySQL as this seems the only database i could find to handle the large datasets i working with. I've created 2 tables...Contact and Company. The Contact contains approx 18MM records, whereas Company has around 3MM records.

I have Primary Key's on each table and put a 2 additional indexes on Contact. When i run the following query, it is taking about 90 minutes to return around 900K records:

Select a.ContactID, a.CompanyID, b.CompanyName, a.FirstName, a.LastName, a.Title, a.Email, a.Level, b.Employees
From contact a, company b
Where a.CompanyID = b.CompanyID
and a.Level = 'VP-Level'

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a ref SECONDARY,level_idx level_idx 7 const 1684874 Using where
1 SIMPLE b eq_ref PRIMARY PRIMARY 4 hip.a.CompanyID 1

This same query without the join to Company takes around 20 Mins:
Select a.ContactID, a.CompanyID, a.FirstName, a.LastName, a.Title, a.Email, a.Level
From contact a
Where a.Level = 'VP-Level'

Contact IndexesTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type
contact 0 PRIMARY 1 ContactID A 18002405 NULL NULL BTREE
contact 1 SECONDARY 1 CompanyID A 1636582 NULL NULL YES BTREE
contact 1 level_idx 1 Level A 18 4 NULL YES BTREE

Company IndexTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type
company 0 PRIMARY 1 CompanyID A 3353584 NULL NULL BTREE

Wondering if there is anything i can do to speed things up a bit or if it's just a product of the large table sizes?

Any help is appreciated as I've almost given myself a concussion banging my head against the wall. Let me know if any add'l info would be needed to evaluate.

Thanks,
-Ed

Hi Ed,
i think i could try to give u a suggest about your problem and what i suppose about query slowliness.

Let's start with the suggest:
Why don't u try to use a left join instead of a from with the two tables? I think that this code should be more linear and could make your query faster than now.

About my supposition, I think that a lot of slowliness should be caused by Where Clause based on a text: of course you've to do on that field your where clause and use an index on this field is the right way to make things better.

Slowliness should also be caused by HW Performance, but you've to do a more intense analysis on that eventually.

Hope to be helpful.
Bye Paolo.

Thanks Pavlov! I replaced my join with a Straight_Join, but still took 90 mins...Explain didn't change, but i ran it anyways.

I have and Index on the Contact.Level field and indexes on Contact.ContactID, Contact.CompanyID tables, and also on the Company.CompanyID table.

Select a.ContactID, a.CompanyID, b.CompanyName, a.FirstName, a.LastName, a.Title, a.Email, a.Level, b.Employees
From hip.contact a straight_join hip.company b on a.CompanyID = b.CompanyID
Where a.Level = 'VP-Level'

Maybe it's a hardware issue? I'm running on Intel i5 M540@2.53GHz processor on 64-bit OS with 8GB Ram.

The only other option i can think of would be to either 1) Create Table of targeted Contacts and then join that table to Company or 2) perhaps add the Company fieds to the Contact table to eliminate the need for the join. Don't really want to go down #2, but if that will speed up my queries through use of single table, i'll consider.

thanks,
-ed