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

New contributor
Androz2091 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
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. – ypercubeᵀᴹ 2 days ago
  • Are these 3 columns text? It would help if you provided the CREATE TABLE script - or the output of \d invited_member_events – ypercubeᵀᴹ 2 days ago
1

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

enter image description here

New contributor
Androz2091 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • 2
    From 552 to 531 ms, not much gain. – ypercubeᵀᴹ 2 days ago
  • Please don't post text as images. Especially not images which have cropped off the most important bits. – jjanes 2 days ago

Your Answer

Androz2091 is a new contributor. Be nice, and check out our Code of Conduct.

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.