mysql multiple queries or multiple 'or' operators

Question

Let's say I have this table:

img_id, img_author, img_path
1     | BillyDoe  | img1.jpg
2     | BillyDoe  | img2.jpg
3     | BillyDoe  | img3.jpg

I run another query that matches some specific criterias and that query returns an array many img_author names like: BillyDoe, JillDoe, JohnDoe etc...

The second thing I'll do is to get all the images from the image table that match the authors names

Would It be better to create a loop with all the author names the previous query returned and run the query multiple times like so

select img_path from images_table where img_author = $curr_author_name

Or I should create a custom function that will join all author names and run a single sql query with multiple 'or' operators like so:

select img_path from images_table where img_author = BillyDoe or img_author =  JillDoe or img_author =  JohnDoe ... x100 times

Will I have any kind of performance loss/gain doing either of these two ways? Or there are better alternatives like multi queries or so. Always talking performance wise, which method would be less resource intense?

Thank you.

Pretty sure you're going to need to escape those strings there, as `img_author=BillyDoe` is not valid SQL unless `BillyDoe` is a column. — tadman, Oct 26 '15 at 20:47
Is the first query to find the authors on the same table or a different table? — Derek, Oct 26 '15 at 20:47
Use one query with `OR`s. It would be better to have author `id`s and use that maybe using an `IN()` clause. — AbraCadaver, Oct 26 '15 at 20:54

Mike Brant · Accepted Answer · 2015-10-26T20:56:27.207

I would do a single query with a join, such that you are querying for the img_author names you are looking for, then join against this table to get the img_path information.

You haven't shown the first query you mention to determine the author names so I can't give you a specific example. BUt that query might look like this:

SELECT
    it.img_path AS img_path
FROM
    whatever_table_is_in_your_first_query AS x
INNER JOIN
    img_table AS it
ON x.author = it.img_author
WHERE
   ... /* whatever criteria you use to get author names */

Doing queries in nested loops should absolutely be considered an anti-pattern to be avoided unless you have a very specific reason to do so and understand the performance trade-offs.

mysql multiple queries or multiple 'or' operators

1 Answers1