Geek Answers Handbook

MySQL: get one row for each item in the IN section efficiently

I have a MySQL database with a large number of tables, and I have two tables that are related to each other in many ways, using the third table. Here is a simplified demonstration of how it looks:

Table 1: books

  • Line 1: ID: 15 Name: Dummy Testbook 1
  • Line 2: ID: 18 Name: Dummy Testbook 4
  • Line 3: ID: 22 Name: Dummy Testbook 6
  • Line 4: ID: 10 Name: Dummy Testbook 8
  • Line 5: ID: 16 Name: Dummy Testbook 15
  • ...

Table 2: Authors

  • Row 1: ID: 4 Name: Dave
  • Line 2: ID: 8 Name: Robert
  • Row 3: ID: 12 Name: Amy

Table 3: books_authors

Line 1: author_id: 4 book_id: 15 Line 1: author_id: 8 book_id: 22 Line 1: author_id: 8 book_id: 10 Line 1: author_id: 12 book_id: 16

What I'm trying to do is get a random list of authors with the last book for each author, so I get a list with all author IDs, using some scripts to create this random list, and then I use the following query:

SELECT `books`.`ID`, `books`.`Name` FROM `books` LEFT JOIN `books_authors` ON `books`.`ID` = `books_authors`.`book_id` LEFT JOIN `authors` ON `books_authors`.`author_id` = `authors`.`ID` WHERE `authors`.`ID` IN(8, 12) LIMIT 1

The problem is that Limit 1 means that I get only one book, and I want to get two books, one for each author. How can I do this without executing a request for each author? (the database is huge, and a request for each author will lead to a server bypass). If I increase the limit, then I do not necessarily get two books one on top of each other, but I can get two books of the same author.

In other words, I want the limit to be on IN, and not on the whole request. Is it possible? and if not, is there an effective way to do what I'm trying to do?

Thanks!

David

+4
source share
3 answers

You can first select the last identifier for each authors book and join it with a book table to get the names. Something like that:

 SELECT `books`.`ID`, `books`.`Name` FROM `books` INNER JOIN ( select max(book_id), author_id from `books_authors` group by author_id) ba ON `books`.`ID` = ba.`book_id` WHERE ba.author_id IN (8, 12)
+2
source

There are several solutions to this problem here , but the bottom line is that if your data set is huge, you are probably best off turning off multiple queries, one per author.

+1
source

This is not quite the answer to this question, but it worked for me for a similar problem.

I have a result set from a query with columns A, B, C, D, E. Since I need one row for any D + E-type result (I don’t care which one), I did this with the function "group D, E "

35.000+ registers, each column was varchar (30), Mysql takes 766 ms to complete this work.

0
source

More articles:


All Articles