MySQL -- Trouble with Indexes

Question

I have a bit of an odd situation and I'm trying to understand it.

I have two tables, I'll list the necessary fields.

products [MyIsam -- due to full text search]  
   product_id [primary index]

Has roughly 1,000,000 rows in it.

website_products [InnoDB]  
    website_id [primary]  
    product_id [primary]

Has roughly 6,000,000 rows in it.

Now when I do queries...

SELECT
    COUNT(`product_id`)
FROM `products`

Returns in 0.109 seconds with the result "1030650"

SELECT
    COUNT(wp.`product_id`)
FROM `website_products` AS wp 
WHERE wp.`website_id` = 1133

Returns in 0.577 seconds with the result "104150"

SELECT
    COUNT(wp.`product_id`)
FROM `website_products` AS wp 
LEFT JOIN `products` AS p ON p.product_id = wp.product_id
WHERE wp.`website_id` = 1133

Returns in 38.173 seconds with result "104150"

An EXPLAIN SELECT yields:

1   SIMPLE  wp  ref PRIMARY,website_id  PRIMARY 4   const   204392  Using index
1   SIMPLE  p   eq_ref  PRIMARY PRIMARY 4   imaginer_system.wp.product_id   1   Using index

Why is it so much longer when I join the table? How can I reduce that?

score 0 · Answer 1 · answered Feb 10 '16 at 03:05

0

For this query:

SELECT COUNT(wp.`product_id`)
FROM `website_products` wp LEFT JOIN
     `products` p
      ON p.product_id = wp.product_id
WHERE wp.`website_id` = 1133;

The optimal indexes are: website_products(website_id, product_id) and products(product_id).

answered Feb 10 '16 at 03:05

Gordon Linoff

1,242,037
58
646
786

I wonder if the issue is having two different storage engines. – Gordon Linoff Feb 10 '16 at 03:20
I'm wondering about that too, but I don't have an easy way to determine it – Kerry Jones Feb 10 '16 at 03:42
1

I'm thinking this might explain some of the problem http://stackoverflow.com/questions/5475283/joining-innodb-tables-with-myisam-tables – Kerry Jones Feb 10 '16 at 03:45
Something else must be going on. Please provide `SHOW CREATE TABLE` for both tables, need to check more info that the limited summary you gave. – Rick James Feb 11 '16 at 21:57

score 0 · Accepted Answer · edited May 23 '17 at 12:23

I found the answer: MyIsam and InnoDB do not place nicely together.

This link helped:
Joining InnoDB tables with MyISAM tables

I converted my products table to be InnoDB as the FullText capabilities are supposed to match MyIsam's, and the search time went from 38 seconds to 0.4

We were running 300 sites on a server that was maxing out a server with 6 CPUs and it dropped to using 1/2 a CPU.

MySQL -- Trouble with Indexes

2 Answers2