0

I have a query with a JOIN on three tables that is taking a very long time to run. I created an index on one of my tables for the foreign key (user_shared_url_id) and two columns (event_result, enabled) in the WHERE clause, so it's an index of three columns total. There seems to be no different from when I simply use an index of the foreign key (user_shared_url_id). The other two tables are using single column indexes. My users table has about 20,000 rows, but the other two tables are quite large, with ~20 million rows. I can't get a query that takes less than a minute or so to finish. Can anyone think of any potential optimizations I can make to speed this up? Are there other indexes or improvements to my custom index that I can work with?

The tables:


 CREATE TABLE `users` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `roles` varchar(500) DEFAULT NULL,
  `first_name` varchar(200) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `org_id` int(11) unsigned NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `org_id` (`org_id`),
  KEY `status` (`status`),
  KEY `org_id_user_id` (`org_id`,`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=162524 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC


 CREATE TABLE `user_shared_urls` (
  `user_id` int(11) unsigned NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `user_shared_url_id` int(11) NOT NULL AUTO_INCREMENT,
  `target_url` text,
  PRIMARY KEY (`user_shared_url_id`),
  KEY `user_id` (`user_id`),
  KEY `user_id_usu_id` (`user_id`,`user_shared_url_id`)
) ENGINE=InnoDB AUTO_INCREMENT=62449105 DEFAULT CHARSET=utf8 |


 CREATE TABLE `user_share_events` (
  `user_share_event_id` int(11) NOT NULL AUTO_INCREMENT,
  `event_result` tinyint(1) unsigned DEFAULT NULL,
  `user_shared_url_id` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`user_share_event_id`),
  KEY `user_shared_url_id` (`user_shared_url_id`),
  KEY `usuid_enabled_result` (`user_shared_url_id`,`enabled`,`event_result`)
) ENGINE=InnoDB AUTO_INCREMENT=35067339 DEFAULT CHARSET=utf8 |

My indexes:

CREATE INDEX org_id_user_id ON users(org_id, user_id);
CREATE INDEX user_id_usu_id ON user_shared_urls(user_id, user_shared_url_id);
CREATE INDEX usuid_enabled_result ON user_share_events(user_shared_url_id,enabled,event_result);

My query:

SELECT
    users.user_id,
    users.user_email "user_email",
    users.roles "role",
    CONCAT(users.first_name, ' ', users.last_name) "name",
    usus.target_url
FROM
    users
    JOIN user_shared_urls usus ON usus.user_id = users.user_id
    JOIN user_share_events uses ON usus.user_shared_url_id = uses.user_shared_url_id 
WHERE
    users.org_id = 1523
    AND
    uses.enabled = '1'
    AND
    uses.event_result = 1

Explain output of the above query:

+----+-------------+-------+------+----------------------------------------------------------------------------------+--------------------+---------+--------------------------------+------+-------------+
| id | select_type | table | type | possible_keys                                                                    | key                | key_len | ref                            | rows | Extra       |
+----+-------------+-------+------+----------------------------------------------------------------------------------+--------------------+---------+--------------------------------+------+-------------+
|  1 | SIMPLE      | users | ref  | PRIMARY,org_id,org_id_user_id                                                    | org_id             | 4       | const                          | 1235 | NULL        |
|  1 | SIMPLE      | usus  | ref  | PRIMARY,user_id,user_id_usu_id                                                   | user_id_usu_id     | 4       | luster.users.user_id           |  213 | NULL        |
|  1 | SIMPLE      | uses  | ref  | user_shared_url_id,user_and_service,result_service_occurred,usuid_enabled_result | user_shared_url_id | 4       | luster.usus.user_shared_url_id |    1 | Using where |
+----+-------------+-------+------+----------------------------------------------------------------------------------+--------------------+---------+--------------------------------+------+-------------+
3 rows in set (0.00 sec)
Improve this question
3
  • 1
    have you try to change the SELECT to SELECT ... FROM users JOIN user_shared_urls usus ON ..... and which MySQL Version you are using Commented Feb 23, 2020 at 1:16
  • MySql version is 5.6.34 Commented Feb 23, 2020 at 18:22
  • Is there any compelling reason to be using MyISAM? It may be faster simply by changing to InnoDB. Commented Feb 24, 2020 at 18:20

2 Answers 2

Reset to default
1

(Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.)

Change that index you added to

INDEX(user_shared_url_id,     -- = and used for the JOIN
      enabled,                -- =
      event_result)           -- Last (not an = test)

The order of columns in an INDEX is important. Start with the columns that are tested for = (or IS NULL).

Then remove the FORCE INDEX and run the EXPLAIN again.

Are these tables in a 1:many relationship? Tell us which way.

Another comment: If event_result really has only two values (true/false) and you are using NULL for false, then change the query from

uses.event_result IS NOT NULL

to

uses.event_result = 1

The point is that the Optimizer likes to optimize =, but sees NOT NULL as being any of 256 possible values; very far from =. With this query change, your index should work. And even be picked without using FORCE.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hey thanks for the response. I made your suggested edits, and added some new indexes that another user suggested. I updated my original post with the updated query, indexes and explain, and I used SHOW CREATE TABLE instead of DESCRIBE for my table output. My explain is showing me that the new indexes are still being ignored without FORCE INDEX. Any suggestions on how to use the indexes without forcing it?
Also, 1 user has many user_shared_urls. 1 user_shared_url has many user_share_events
@AndrewKirchmyer - Does it actually run faster with the FORCE INDEX than without?
No, it doesn't seem to run any faster with FORCE INDEX
@AndrewKirchmyer - The Optimizer eschews an index (thereby tempting you to use FORCE) when it decides that a table scan would probably be faster than bouncing between the index BTree and the data BTree. The Optimizer usually estimates correctly. But then, I come along and say "Force may be better today, but tommorrow, after the data has grown, it may no longer by the better."
1

For this query:

SELECT u.user_id, u.user_email, u.roles "role",
       CONCAT(u.first_name, ' ', u.last_name) "name",
       usu.target_url
FROM user_shared_urls usu JOIN
     users u
     ON usu.user_id = u.user_id JOIN
     user_share_events usev
     ON usus.user_shared_url_id = usev.user_shared_url_id 
WHERE u.org_id = 1010 AND
      usev.event_result IS NOT NULL AND
      usev.enabled = 1;

Probably the best indexes are:

  • users(org_id, user_id)
  • user_shared_urls(user_id, user_shared_url_id)
  • user_share_events(user_shared_url_id, enabled, event_result)

This assumes that the filtering on org_id is more selective than the other filters.

Improve this answer

5 Comments

Hey thanks for the response. I added your suggested indexes, but my explain output is showing me that these indexes are being ignored. Do you know why they might be ignored without forcing them? I updated my query, indexes and explain output above.
@AndrewKirchmyer . . . You don't have very much data (~1k rows), so MySQL may not see the advantage of indexes.
My users table has a few thousand rows, the other two tables have over 20 million rows. What does the row count in the explain mean?
@AndrewKirchmyer . . . The biggest number I see in the explain plan is 1235.
EXPLAIN's "Rows" is an estimate of the number of rows that will be fetched from that table. Usually, the Optimizer uses NLJ (qv), stepping from one table to the next. Multiply together the numbers in the Rows column to get a very crude estimate of time taken to run the query. Better indexes often decrease these numbers.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.