Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following tables:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account_data` text COLLATE utf8_unicode_ci,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `twitter_username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `crypted_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password_salt` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `persistence_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `single_access_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `perishable_token` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `login_count` int(11) NOT NULL DEFAULT '0',
  `failed_login_count` int(11) NOT NULL DEFAULT '0',
  `last_request_at` datetime DEFAULT NULL,
  `current_login_at` datetime DEFAULT NULL,
  `last_login_at` datetime DEFAULT NULL,
  `current_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `last_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT '0',
  `referrer_id` int(11) DEFAULT NULL,
  `partner` tinyint(1) DEFAULT '0',
  `subscription_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'free',
  `workflow_state` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `persona_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `persona_index` (`persona_id`)
) ENGINE=InnoDB 

and the table:

CREATE TABLE `user_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `module` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `data` text COLLATE utf8_unicode_ci,
  `timestamp` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id_index` (`user_id`),
  KEY `action_type_index` (`action_type`),
  KEY `user_action_type_index` (`user_id`,`action_type`),
  KEY `timestamp_index` (`timestamp`),
  KEY `user_id_timestamp_index` (`user_id`,`timestamp`)
) ENGINE=InnoDB 

the problem is with the following query:

    SELECT user_actions.*, users.twitter_username, users.email FROM `user_actions` 
INNER JOIN users ON (user_actions.user_id=users.id) ORDER BY timestamp DESC LIMIT 0, 30

here is the explain:

user_actions    
The table was retrieved with this index: user_id_timestamp_index
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 76 rows of this table were scanned.
users   
This table was retrieved with a full table scan, which is often quite bad for performance, unless you only retrieve a few rows.
The table was retrieved with this index:
No index was used in this part of the query.
A temporary table was created to access this part of the query, which can cause poor performance. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.
MySQL had to do an extra pass to retrieve the rows in sorted order, which is a cause of poor performance but sometimes unavoidable.
You can speed up this query by querying only fields that are within the index. Or you can create an index that includes every field in your query, including the primary key.
Approximately 3445 rows of this table were scanned.

this query takes a long time to execute, any ideas how to improve?

share|improve this question
1  
Add the EXPLAIN output please. And format the question so your query isn't all on one line... –  gbn Jan 6 '12 at 9:42
1  
I had never seen an Explain like that. Interesting that it says "You can speed up this query by ..." –  Purefan Jan 6 '12 at 13:22
    
its the explain from newrelic –  Ran Jan 6 '12 at 13:39
add comment

2 Answers 2

up vote 4 down vote accepted

Here is your original query:

SELECT
    user_actions.*,
    users.twitter_username,
    users.email
FROM
    `user_actions`  
    INNER JOIN users
    ON (user_actions.user_id=users.id)
    ORDER BY timestamp
    DESC LIMIT 0, 30
;

First thing I notice is that you are joining two whole tables. Since you only need twitter_username and email from the users table, you should only join from users using three columns : id, twitter_username and email.

Second thing is the LIMIT clause. It is being executed after the join. You should execute it before the join. In your case, you are requesting for the 30 most recent user actions. If you can guarantee that only 30 rows are retreived from user_actions, the join should operate much faster.

If you read the answer from @DTest, his first two bulletpoints already tell you what's wrong the query because of the actions mysql will take in gathering data from each table. The key is to understand what the temp tables will look like while the query is being processed and where the data will reside (memory or disk).

What you need to do is refactor the query to fool the MySQL Query Optimizer. Force the query to produce smaller temp tables. In most cases, config changes in my.cnf should make a dramamtic difference. In other cases, such as this one, refactoring the query may be sufficient.

Here is my proposed change to your query that should work faster:

SELECT
    ua.*,
    u.twitter_username,
    u.email
FROM
    (SELECT * FROM `user_actions`
    ORDER BY timestamp DESC LIMIT 30) ua
    LEFT JOIN
    (SELECT id,twitter_username,email FROM `users`) u
    ON (ua.user_id=u.id)
;

Here are reasons for refactoring the query:

REASON #1

If you look at inline table ua, I retrieve only 30 rows using LIMIT. This will happen no matter how big the user_actions table gets. It is already ordered because the ORDER BY timestamp DESC happens before the LIMIT.

REASON #2

If you look inline table u, it has id,twitter_username,email. The id is needed to implement the join.

REASON #3

I use LEFT JOIN instead of INNER JOIN for two(2) reasons:

  1. Preserve the order of the query based on ua
  2. Display all user actions in case the user_id in the ua no longer exists in the users tables.

Doing these things will force the temp tables to be smaller. Nevertheless, you will still need implement bulletpoint #3 from @DTest's answer to preempt having temp tables land on disk.

share|improve this answer
add comment

Well the major issue is that since your query does not have any filtering on it (no WHERE statement), it places all the rows with columns user_actions.*, twitter_username, email into a temporary table to do the sorting.

So the first thing I would do is attempt to limit the number of rows that go into your result set. For example, I would say adding a WHERE timestamp > DATE_SUB(NOW(), INTERVAL 7 DAY) to get only results within last 7 days (if that's acceptable to your use-case).

Next, I would change the query to only pull the required columns from user_actions to reduce the amount of information needed to put into a temporary table.

Now that you may or may not have removed rows/columns that need to be placed in the temporary table to be sorted, let us look at how MySQL handles temporary tables. From the documentation on the tmp_table_size variable (emphasis added):

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)1 If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk MyISAM table.

First, let me point out the caveat represented by the superscript 1: The size of the temporary table created in memory is the minimum of either tmp_table_size or max_heap_table_size, so if you increase one, make sure to increase the other.

If the amount of your data exceeds the size of the minimum of those two variables, it will be placed on disk. Disk is slow. Don't do disk if you can avoid it!

To recap:

  • Limit the amount of rows you are sorting on, using WHERE. Even though you are doing a LIMIT, all rows are still being placed in temporary table to sort.

  • Limit the number of columns you are requesting. If you don't need them, don't ask for them.

  • Last resort, increase the size of tmp_table_size and max_heap_table_size if the query is increasing your Created_tmp_disk_tables status variable. Also, don't increase this drastically. It might have performance impact, depending on your hardware and the amount of RAM you have on your server.

share|improve this answer
    
Hey @DTest, I mentioned parts of your answer in my answer. +1 to avoid copyright infringement !!! –  RolandoMySQLDBA Jan 6 '12 at 17:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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