Slow mySQL query when using two indexed columns

Question

I have a mySQL table (myISAM) containing approximately two million rows - name, address, company data. The first name and surname are held in separate columns, so I also have a second table (linked by the primary key of the first) which holds a single full name column.

The first name, surname, and company name (among others) in the first table are indexed, as is the full name column in the secondary table.

Taking this query as a starting point:

SELECT * FROM table_a INNER JOIN table_b ON table_a.ID = table_b.ID WHERE....

searching exact match or even after-like on the name columns works in milliseconds:

....table_a.first_name = 'Fred'
....table_a.surname = 'Bloggs'
....table_b.fullname = 'Fred Bloggs'
....table_a.first_name LIKE 'Mike%'

just a few examples.

Throw the COMPANY NAME in there as well..... the query suddenly takes 15 to 20 seconds:

....table_a.first_name = 'Fred' OR table_a.company_name = 'Widgets Inc'

for example

Both fields are indexed, it's an exact match.... why would the addition of a second indexed search column slow things down so much? Have I missed something about my table design?

Examples follow - there are a few other tables joined but I'm not sure these are affecting performance: Example of name-only query which returns in 0.0123 seconds:

SELECT SQL_CALC_FOUND_ROWS 
    webmaster.dupe_master_id AS webmaster_id, 
    webmaster.first_name, 
    webmaster.family_name, 
    webmaster.job_title, 
    webmaster.company_name, 
    webmaster.address_1, 
    webmaster.address_2, 
    webmaster.town_city, 
    webmaster.state_county, 
    webmaster.post_code, 
    webmaster.email, 
    webmaster.ignored, 
    countries.country_name, 
    GROUP_CONCAT(DISTINCT titles.code ORDER BY code ASC) AS sub_string, 
    '' AS expo_string 
FROM 
    (`webmaster`) 
    LEFT JOIN `countries` ON `countries`.`country_id` = `webmaster`.`country_id` 
    LEFT JOIN `red_subscriptions` ON `red_subscriptions`.`webmaster_id` = `webmaster`.`webmaster_id` AND red_subscriptions.subscription_status_id = 2 
    LEFT JOIN `titles` ON `titles`.`title_id` = `red_subscriptions`.`title_id` 
    LEFT JOIN `webmaster_tags` ON `webmaster_tags`.`webmaster_id` = `webmaster`.`webmaster_id` 
    LEFT JOIN `tags` ON `tags`.`tag_id` = `webmaster_tags`.`tag_id` 
    INNER JOIN `webmaster_search_data` ON `webmaster`.`webmaster_id` = `webmaster_search_data`.`webmaster_id` 
WHERE 
    (full_name = '<name>') 
GROUP BY 
    `webmaster`.`dupe_master_id` 
LIMIT 50

Add in company_name (also indexed) and the query time goes through the roof:

SELECT SQL_CALC_FOUND_ROWS 
    webmaster.dupe_master_id AS webmaster_id, 
    webmaster.first_name, 
    webmaster.family_name, 
    webmaster.job_title, 
    webmaster.company_name, 
    webmaster.address_1, 
    webmaster.address_2, 
    webmaster.town_city, 
    webmaster.state_county, 
    webmaster.post_code, 
    webmaster.email, 
    webmaster.ignored, 
    countries.country_name, 
    GROUP_CONCAT(DISTINCT titles.code ORDER BY code ASC) AS sub_string, 
    '' AS expo_string 
FROM 
    (`webmaster`) 
    LEFT JOIN `countries` ON `countries`.`country_id` = `webmaster`.`country_id` 
    LEFT JOIN `red_subscriptions` ON `red_subscriptions`.`webmaster_id` = `webmaster`.`webmaster_id` AND red_subscriptions.subscription_status_id = 2 
    LEFT JOIN `titles` ON `titles`.`title_id` = `red_subscriptions`.`title_id` 
    LEFT JOIN `webmaster_tags` ON `webmaster_tags`.`webmaster_id` = `webmaster`.`webmaster_id` 
    LEFT JOIN `tags` ON `tags`.`tag_id` = `webmaster_tags`.`tag_id` 
    INNER JOIN `webmaster_search_data` ON `webmaster`.`webmaster_id` = `webmaster_search_data`.`webmaster_id` 
WHERE 
    (full_name = '<name>' OR company_name '<name>') 
GROUP BY 
    `webmaster`.`dupe_master_id` 
LIMIT 50

EXPLAIN on full_name only:

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE webmaster_search_data ref webmaster_id,full_name full_name 302 const 94 Using where; Using temporary; Using filesort 1 SIMPLE webmaster eq_ref PRIMARY PRIMARY 4 webmaster_search_data.webmaster_id 1
1 SIMPLE countries eq_ref PRIMARY PRIMARY 2 webmaster.country_id 1
1 SIMPLE red_subscriptions ref webmaster_id,subscription_status_id webmaster_id 4 webmaster_search_data.webmaster_id 1
1 SIMPLE titles eq_ref PRIMARY PRIMARY 2 red_subscriptions.title_id 1
1 SIMPLE webmaster_tags ref webmaster_id webmaster_id 4 webmaster_search_data.webmaster_id 5
1 SIMPLE tags eq_ref PRIMARY PRIMARY 2 webmaster_tags.tag_id 1 Using index

Explain when company_name is added:

1 SIMPLE webmaster index PRIMARY,company_name dupe_master_id 4 NULL 2072015 Using filesort 1 SIMPLE countries eq_ref PRIMARY PRIMARY 2 webmaster.country_id 1
1 SIMPLE red_subscriptions ref webmaster_id,subscription_status_id webmaster_id 4 webmaster.webmaster_id 1
1 SIMPLE titles eq_ref PRIMARY PRIMARY 2 red_subscriptions.title_id 1
1 SIMPLE webmaster_tags ref webmaster_id webmaster_id 4 webmaster.webmaster_id 5
1 SIMPLE tags eq_ref PRIMARY PRIMARY 2 webmaster_tags.tag_id 1 Using index 1 SIMPLE webmaster_search_data eq_ref webmaster_id,full_name webmaster_id 4 webmaster.webmaster_id 1 Using where

Answer 1

MySQL cannot use two indexes at once. When you throw in the company name, MySQL cannot use the index on Firstname, Lastname anymore because now there are more columns it has to check to get an exact result.

It is probably doing a full table scan.

You could split your queries up by doing a Union, that way you can use both columns with the index.

SELECT * FROM
( SELECT * FROM table_a 
  INNER JOIN table_b ON table_a.ID = table_b.ID 
  WHERE table_a.first_name = 'Fred' 
  UNION
  SELECT * FROM table_a 
  INNER JOIN table_b ON table_a.ID = table_b.ID
  WHERE table_a.company_name = 'Widgets Inc'
) sub;

Each query should be evaluated separately and use the adequate index. THe UNION will take care of doubles, so you will in the end have the same result.