Are the index here will suffice to make the query faster?

Question

This is the continuation of the question, What column/s do I need an index for my queries?, as Rick James advices me to ask a new question.

Here is the simpler code from my queries, and how will I implement the index in the column based on from what I understand while studying about it. Note: fields and values are changed for example purposes only.

1) INDEX in id

SELECT a, b ... FROM users_table WHERE id = '1'

2) INDEX in user_email

SELECT a, b ... FROM users_table WHERE user_email = '[email protected]'

3) INDEX in user_link

SELECT a, b ... FROM users_table WHERE user_link = 'www.somelink.com'

4) INDEX in user_recover_link

SELECT a, b ... FROM users_table WHERE user_recover_link = 'code'

5) INDEX in id, side note: I may try Rick James' answer related for pagination.

SELECT a, b ... FROM posts_table WHERE id > 0 ORDER BY date DESC LIMIT 0, 10

6) INDEX in id

SELECT a ... FROM posts_table WHERE id > 0

7) INDEX in category

SELECT a, b ... FROM posts_table WHERE category = 'some-category'
     ORDER BY date DESC LIMIT 0, 10

8) INDEX in category

SELECT a, b ... FROM posts_table WHERE category = 'some-category'

9) INDEX in post_url

SELECT a, b ... FROM posts_table WHERE post_url = 'some-url'

10) INDEX in user_url

SELECT a, b ... FROM users_table WHERE user_url = 'some-url'

11) INDEX in user_id

SELECT a ... FROM posts_table WHERE user_id = '1' ORDER BY date DESC

12) INDEX in user_id

SELECT a ... FROM posts_table WHERE user_id = '1'

13) INDEX in msg_receiver_id, msg_sender_id, date

SELECT a, b FROM msgs_table m1
    WHERE (m1.msg_receiver_id = '1' OR m1.msg_sender_id = '1')
      AND EXISTS (SELECT max(m2.date) maxDate FROM messages m2
            WHERE m1.msg_receiver_id = m2.msg_receiver_id
              AND m1.msg_sender_id = m2.msg_sender_id
            GROUP BY msg_receiver_id, msg_sender_id
            HAVING m1.date = maxDate)
    ORDER BY m1.date DESC

14) INDEX in id

SELECT a, b ... FROM users_table WHERE id IN ('1', '2', ...)
    ORDER BY FIELD (id, '1', '2', ...)

15) INDEX in msg_url

SELECT a, b ... FROM msgs_table
    WHERE msg_url IN ('some-url-1', 'some-url-2')
    ORDER BY date ASC

16) INDEX in msg_receiver_id, msg_status

SELECT a, b ... FROM msgs_table
    WHERE msg_receiver_id = ?
      AND msg_status = 'unread'

NOTE: Since id is a PRIMARY KEY I do not need to use index anymore. Is this an okay to increase the queries speed performance?

Answer 1

1,2,3,4,9,10 -- all the same pattern; all good. The PRIMARY KEY qualifies as an INDEX (and UNIQUE).

The PK is often faster than a secondary key. So, do not add INDEX(id) to a table that already has PRIMARY KEY(id). There can be only one PK on a table.

6 - good

7,11 - Needs improvement: Composite INDEX(category, date), INDEX(user_id, date)

8,12 - handled by indexes for 7,11

14,16 - OK as you state.

edit 15 - INDEX(msg_url, date)

13 - discussed in the other thread

To summarize:

users_table:

PRIMARY KEY(id),
INDEX(user_email),
INDEX(user_link),
INDEX(user_recover_link)

post_table:

PRIMARY KEY(id),
INDEX(category, date),  
INDEX(post_url),
INDEX(user_url),
INDEX(user_id, date),
INDEX(msg_receiver_id, msg_sender_id, date)

msg_table

PRIMARY KEY(id),  ??
INDEX(msg_receiver_id, msg_status),
INDEX(msg_url, date)

Answer 2

Your question is a bit unclear, and I'll explain why:

1. In a typical RDBMS, unless you change the default, a PK is going to yield a clustered index, so your ID (PK) is an index already!
2. You mention you want to add indexes, but you don't mention if it is a clustered index or non-clustered.
3. The rationale/logic behind indexing depending entirely on the CONTENT and USAGE of the database/table, and not on the schema. For example, if the field name contains 3000 rows, but just 2 distinct values, then you'd be silly to create a clustered index.