0

This is the query I want to optimize:

SELECT *
    FROM invited_member_events
    WHERE (user_id = '422820341791064085'
    OR inviter_user_id = '422820341791064085')
    AND guild_id = '638685268777500672';

I created an index for the guild_id column, a second index with the columns user_id_inviter_user_id, a third index with the user_id column. And it does not use them, it only uses the guild_id one. Do you know how can I create an index so it works with the OR condition?

enter image description here

as you can see, only the guild_id index is used, not the other ones.

the other indexes: enter image description here

2
  • 1
    I would try with 2 indexes, on (guild_id, user_id) and on (guild_id, invited_user_id). If they are not used, I'd retry (with those same 2 indexes) rewriting the query without the OR using UNION. Jun 11, 2021 at 12:57
  • Are these 3 columns text? It would help if you provided the CREATE TABLE script - or the output of \d invited_member_events Jun 11, 2021 at 12:58

1 Answer 1

1

I added a idx_invited_member_events_inviter_user_id, and it seems to be working.

enter image description here

2
  • 2
    From 552 to 531 ms, not much gain. Jun 11, 2021 at 12:59
  • 1
    Please don't post text as images. Especially not images which have cropped off the most important bits.
    – jjanes
    Jun 11, 2021 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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