Very slow joins table (EXPLAIN included)

Databases
1

Hi,

I’m very confused about my dirty quires. So, I don’t know what’s wrong with my JOINS why they are so slow?

I have 2 table with large data:

  • forum_messages :: 267,808+ row total
  • forum_users :: 332,042+ total

Here is my Query:

SELECT
users.type_id,
                users.full_name,
                users.user_id,
                msgs.message_id,
                msgs.message_body,
                msgs.msg_is_read,
                msgs.user_send_id,
                UNIX_TIMESTAMP(msgs.sent_date) AS snt_date
            FROM
                `forum_messages` AS msgs  INNER JOIN `forum_users` AS users ON msgs.user_send_id = users.user_id
            WHERE
                msgs.user_receive_id = 1
                AND msgs.msg_del_receive = 1
                AND msgs.is_shows = 1
            ORDER BY msgs.sent_date DESC LIMIT 10

forum_messages Indexes:

forum_messages EXPLAIN:

forum_users Indexes:

Is there any idea please?

2

Try adding an index on user_send_id?

3

There’s already user_send_id index.

4

Ah yes, I missed that one.

5

Up…