Mysql - INNER JOIN - INDEX

Asked

Viewed 250 times

-2

Guys, I got one query here very simple with JOIN, Because banks have a lot of records, I need to index... But I’m getting it for kct... What is the best way to index in a JOIN? (That one query takes 0.26)

SELECT r.* FROM rooms r JOIN users u ON r.owner=u.id WHERE u.id='697' LIMIT 50;

I have made the following indexs:

CREATE INDEX idx_users ON users(username);
CREATE INDEX idx_own ON rooms(owner);

But no results whatsoever... How to proceed?

mysql sql database mariadb

  • The only way I imagine, is if the Owner in Rooms and id in users are indices, because username, has no indexing effect in this context.

2 answers

1

In his query you are not using the table users to get no data, so you can change your SELECT for:

SELECT r.*
  FROM rooms r
 WHERE r.owner = '697'
 LIMIT 50;

So you will have the same result but without the need to link one table to another.

  • I only took a part of the query to give an example, because the rest was "indifferent" (since, the query was locking in this part)

  • @Weversonfernandes becomes complicated to give an answer without you giving all the parameters of the question...

0

In case someone comes here to consult, I managed to arrange: My tables did not have foreign keys (I created referring to the search) and one of the fields was like Varchar(75) and it stored integers. After the change, the query time was 0.00

Browser other questions tagged mysql sql database mariadb

You are not signed in. Login or sign up in order to post.