0

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query:

EXPLAIN SELECT
    *
FROM
    tusers PARTITION (p362) tu FORCE INDEX (index_tu_group_id_email)
    JOIN users PARTITION (p362) u FORCE INDEX (cc_group_email_index)
      ON u.group_id=tu.group_id 
      AND tu.email_address=u.email
      AND u.group_id = 362 
WHERE
    tu.appliation_id=253555;

+----+-------------+-------+------+-------------------------+-------------------------+---------+-----------------------------+---------+-------------+
| id | select_type | table | type | possible_keys           | key                     | key_len | ref                         | rows    | Extra       |
+----+-------------+-------+------+-------------------------+-------------------------+---------+-----------------------------+---------+-------------+
|  1 | SIMPLE      | u     | ref  | cc_group_email_index    | cc_group_email_index    | 4       | const                       | 8282617 | Using where |
|  1 | SIMPLE      | tu    | ref  | index_tu_group_id_email | index_tu_group_id_email | 247     | const,db.u.email            |       8 | Using where |
+----+-------------+-------+------+-------------------------+-------------------------+---------+-----------------------------+---------+-------------+

I included the FORCE INDEX on tusers here just to simplify the output, including or excluding that makes no difference on the query plan.

When I run the same query on my local database (with significantly less data) the query uses the index, and it provides the performance benefit you'd expect from doing so. One other difference I noticed when running locally: the explain output starts with the tusers filter, and then uses the index on the users table -- I suspect that is related to my issue, but I'm not seeing why/how.

Production database (where the query is failing) has ~300,000,000 rows in the tusers table, and the users table has ~175,000,000 rows. Local database only has a few hundred thousand rows in each. MySQL versions are the 5.6.19 on production, 5.6.10 locally. The partitions in question on production have ~17,000,000 rows (users) and 7,600,000 rows (tusers).

Running a query directly against the email column in the users table makes use of the index:

EXPLAIN SELECT
    *
FROM users FORCE INDEX (cc_group_email_index)
WHERE group_id=362 and email='emailaddress@gmail.com'

+----+-------------+-------+------+----------------------+----------------------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref         | rows | Extra       |
+----+-------------+-------+------+----------------------+----------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | users | ref  | cc_group_email_index | cc_group_email_index | 407     | const,const |    1 | Using where |
+----+-------------+-------+------+----------------------+----------------------+---------+-------------+------+-------------+

I don't think this is related, given I'm forcing the index, but I noticed it so including just in case: cc_group_email_index is composed of two keys, each of which has a cardinality listed of 9, even though the cardinality of both columns is significantly higher. I also have another nearly identical index on a varchar column "raw_id" which when replaced in the above example works perfectly. The only difference between the two columns / indexes is: the raw_id column is varchar(255), and the index is defined to only use the first 60 characters:

KEY `cc_group_email_index` (`group_id`,`email`),
KEY `cc_group_raw_id_index` (`group_id`,`raw_id`(60))

The email address column on tusers is 255 characters (which doesn't match users), though I wouldn't think this matters (and in fact, it does not impact things locally).

Any help/suggestions are HUGELY appreciated.

UPDATE

As requested, here is the table schema:

users:

+---------------------------------+--------------+------+-----+------------+-------+
| Field                           | Type         | Null | Key | Default    | Extra |
+---------------------------------+--------------+------+-----+------------+-------+
| group_id                        | int(11)      | NO   | PRI | NULL       |       |
| internal_user_id                | int(11)      | NO   | PRI | NULL       |       |
| create_date                     | date         | NO   |     | 1000-01-01 |       |
| first_trans_date                | date         | NO   |     | 1000-01-01 |       |
| first_trans_year                | smallint(6)  | NO   |     | 0          |       |
| first_trans_month               | tinyint(4)   | NO   |     | 0          |       |
| last_trans_date                 | date         | NO   |     | 1000-01-01 |       |
| first_purchase_revenue_in_cents | bigint(20)   | NO   |     | 0          |       |
| first_purchase_profit_in_cents  | bigint(20)   | NO   |     | 0          |       |
| first_name                      | varchar(50)  | YES  |     | NULL       |       |
| last_name                       | varchar(50)  | YES  |     | NULL       |       |
| raw_id                          | varchar(255) | YES  |     | NULL       |       |
| address_1                       | varchar(200) | YES  |     | NULL       |       |
| city                            | varchar(75)  | YES  |     | NULL       |       |
| state                           | varchar(50)  | YES  |     | NULL       |       |
| zip                             | varchar(20)  | YES  |     | NULL       |       |
| phone                           | varchar(20)  | YES  |     | NULL       |       |
| email                           | varchar(100) | YES  |     | NULL       |       |
| is_opt_in                       | tinyint(1)   | YES  |     | NULL       |       |
| mail_merge_1                    | varchar(255) | YES  |     | NULL       |       |
+---------------------------------+--------------+------+-----+------------+-------+

tusers:

+--------------------------+--------------+------+-----+---------+----------------+
| Field                    | Type         | Null | Key | Default | Extra          |
+--------------------------+--------------+------+-----+---------+----------------+
| id                       | int(11)      | NO   | PRI | NULL    | auto_increment |
| group_id                 | int(11)      | NO   | PRI | NULL    |                |
| application_id           | int(11)      | YES  | MUL | NULL    |                |
| group_user_id            | int(11)      | YES  |     | NULL    |                |
| raw_id                   | varchar(255) | YES  |     | NULL    |                |
| email_address            | varchar(255) | YES  |     | NULL    |                |
| first_name               | varchar(255) | YES  |     | NULL    |                |
| last_name                | varchar(255) | YES  |     | NULL    |                |
| mail_merge_1             | varchar(255) | YES  |     | NULL    |                |
+--------------------------+--------------+------+-----+---------+----------------+

Cardinality:

tusers: index_tu_group_id_email - 2,500,000 users: 17,000,000

  • Please add the tables' structure and more information about value cardinality (how many rows expected for a particular value on each column and on combinations of certain columns). – jynus Sep 17 '14 at 7:44
  • Updated to include this info. – Jon Sep 17 '14 at 20:02
1

This is just a guess, as I do not have all info, but you probably would be better by doing:

EXPLAIN SELECT STRAIGHT_JOIN
    *
FROM
    tusers PARTITION (p362) tu
    JOIN users PARTITION (p362) u
      ON u.group_id=tu.group_id 
      AND tu.email_address=u.email
      AND tu.group_id = 362 
WHERE
    tu.application_id=253555;

Note the STRAIGHT_JOIN, that may not be needed -if it is needed, then I may have assumed wrongly- and the tu.group_id comparison (that, again, shouldn't be needed).

Then using the following keys:

(tu.application_id, tu.group_id, tu.email_address)
(u.group_id, u.email)

However, if the number of records to be returned is 2.5M, as your cardinality suggests, then do not expect this to be fast... this is a pure IO math.

There are many other things that clicks me as problems, but I cannot say for sure without access.

Those could be even more effective if you didn't do a SELECT *.

Another thing is that varchar(255) is usually a bad idea.

  • Thanks! I tried STRAIGHT_JOIN and the query plan got worse. I'm not quite following the rest of your note? What do you mean by "Then use the following keys"? – Jon Sep 18 '14 at 18:48
  • Well, for sure that won't work without the rest of the recommendations (query change and indexes). Even then, I cannot assure you it will be faster as you didn't provide the asked cardinality. "Then use the following keys" means creating those indexes and making sure that they are used. – jynus Sep 19 '14 at 8:29
  • Sorry, I'm not following what the query change is? I've already got those keys in place and I changed the index to only look at the first 60 characters of the email field on users email, to no avail. Can you explain what you mean by not providing the asked cardinality? Which cardinality is missing? – Jon Oct 9 '14 at 5:04
  • I just discovered what I believe to be part of the problem: users has many records where email is '', more to come. – Jon Oct 9 '14 at 5:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.