CODEWITHSUNDEEP
phpmysqlperformance
Musterknabe
Musterknabe

Reputation: 6081

Improve performance of SQL Query to select ids from different tables in multiple columns

Let us just imagine I have the following table structure

Table: images

id   path   

1    x  
2    x  
3    x  
4    x  
5    x  
6    x  
7    x  
8    x

Table: user

id    image imageSmall

1     1     1
2     2     2
3     4     4

Table: books

id    image imageSmall

1     5     5
2     6     6
3     8     8

I now want to get the ID of every image used in other tables. I made this query here

SELECT id FROM images WHERE id IN (SELECT image FROM user) OR id IN (SELECT imageSmall FROM user) OR id IN (SELECT image FROM books) OR id IN (SELECT imageSmall FROM books);

The problem I see here, is that, when I have a large amount of data, this query could be very time consuming and not performant at all because of the many IN parts of the query. Is there a way to improve the performance of this query?

Upvotes: 0

Views: 248

Answers (2)

beerwin
beerwin

Reputation: 10327

Use joins instead of that many select in selects, and DISTINCT to return unique values:

SELECT DISTINCT(i.id) FROM images i 
INNER JOIN `user` u 
INNER JOIN `books` b 
WHERE b.image=i.id OR b.imageSmall=i.id OR u.image=i.id OR u.imageSmall=i.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I would phrase this using exists rather than in:

SELECT id
FROM images i
WHERE EXISTS (SELECT 1 FROM user u WHERE u.image = i.id) OR
      EXiSTS (SELECT 1 FROM user u WHERE u.imageSmall = i.id) OR
      EXISTS (SELECT 1 FROM books b WHERE b.image = i.id);

For performance, be sure that you have the following indexes:

create index idx_user_image on user(image);
create index idx_user_imageSmall on user(imageSmall);
create index idx_books_image on books(image);

Upvotes: 2

Related Questions