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.

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.

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.

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:

Preserve the order of the query based on ua

Display all user actions in case the user_id in the ua no longer exists in the users tables.

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.)1If 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.