1

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 = 'me@email.com'

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?

1
  • 1
    These are not "Dumb Questions", they are good questions for noobies to study and learn from. ;)
    – Rick James
    Jan 11 '17 at 18:47
2

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)
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Jan 17 '17 at 2:30
1

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.
3
  • 2
    InnoDB really needs a PRIMARY KEY. In MySQL, a PK is, by definition, clustered and UNIQUE.
    – Rick James
    Jan 11 '17 at 4:57
  • 1
    "In a typical RDBMS, unless you change the default, a PK is going to yield a clustered index" Not really, unless "typical" means "SQL Server or MySQL". And the question is specifically about MySQL, so I wonder why you need to comment about "typical DBMS" Jan 11 '17 at 11:04
  • 1
    SQL Server and MySQL form a vast majority of database platforms out there - therefore, quite typical :P Jan 11 '17 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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