CODEWITHSUNDEEP
mysqloptimizationquery-optimization
gregavola
gregavola

Reputation: 2539

Optimization of a MySQL Query with Simple Join across 3 tables

I have the following Database tables:

users
user_id (INT) (PRIMARY), is_cool (TINY) (1 or 0)

INDEX ON is_cool

activity 
activity_id (INT) (PRIMARY), user_id (INT), item_id (INT), created_at (DATETIME)

INDEX on item_id
INDEX on user_id
INDEX on created_at

items
item_id (INT) (PRIMARY), item_name VARCHAR(255), item_parent_id INT()

INDEX ON item_parent_id

I'm trying to do simple join on the these tabels, and I'm getting long query times (> 2sec) by query is below:

SELECT i.item_name, a.activity_id, a.user_id, a.created_at
FROM activity as a
INNER JOIN item as i on a.item_id = i.item_id
INNER JOIN users as u on u.user_id = a.user_id
WHERE i.item_parent_id = 1 and u.is_cool = 1
ORDER by a.created_at DESC
LIMIT 0, 25

Any suggestions on how to improve this? It seems to be hanging due the large nature of the DB. For smaller records it works fine, but for records with 100k records - its taking a long time process.

Upvotes: 0

Views: 859

Answers (2)

newtover
newtover

Reputation: 32094

The execution plan here depends on the data distribution in your db, but MySQL will hardly pick the right index here anyway.

Problem

You basicaly want to iterate through all activities sorted by created_at which conform to conditions in other tables. Most probably, MySQL will treat the condition i.item_parent_id = 1 more selective and start joining from the item table. That is, sorting is going to be on a field in a joined table which results in filesorts and bad performance if the join produces most of the rows from activities table. It would be better if you showed us the result of the EXPLAIN.

Solution

If the result of activity JOIN item produces few rows (say, less than 1000), I would just create the following indexes: the one you already have on item_parent_id, a composite index on activity (item_id, created_at), and (user_id, is_cool) on users.

If the result of activity JOIN item produces many rows (more probable), I would create (item_id, created_at) on activity, (item_id, item_parent_id) on item, (user_id, is_cool) on users, and add a STRAIGHT_JOIN option to the query:

SELECT STRAIGHT_JOIN i.item_name, a.activity_id, a.user_id, a.created_at
FROM activity as a
INNER JOIN item as i on a.item_id = i.item_id
INNER JOIN users as u on u.user_id = a.user_id
WHERE i.item_parent_id = 1 and u.is_cool = 1
ORDER by a.created_at DESC
LIMIT 0, 25;

Upvotes: 2

Kris
Kris

Reputation: 41837

Firstly, I can't really test this without the actual database to be sure, but i'd try moving as many as possible of the where clauses to the appropriate join clauses.

something like this may help:

SELECT 
      i.item_name
    , a.activity_id
    , a.user_id
    , a.created_at
FROM activity as a
INNER JOIN item as i 
    ON  a.item_id = i.item_id
    AND i.item_parent_id = 1
INNER JOIN users as u 
    ON u.user_id = a.user_id 
    AND u.is_cool = 1
ORDER by a.created_at DESC
LIMIT 0, 25;

Upvotes: 1

Related Questions