associative tables, query optimization 1

[cristiancodreanu]

Hello
I am using associative tables in a database and I have problems when I have to simultaneously query multiple tables.

As a simplified sketch of what I mean, these tables are given:

books:
book_id, book_title, book_author

authors:
author_id, author_name

books_authors_assoc:
books_authors_assoc_id, book_id, author_id

pubhouses:
pubhouse_id, pubhouse_name

books_pubhouses_assoc:
books_pubhouses_id, book_id, pubhouse_id

Supposing I want to display all the books whose author name ends with "escu", ordered by, say, publishing house, I will write:

SELECT books.book_title, books.book_author, authors.author_name, pubhouses.pubhouse_name
FROM books, authors, books_authors_assoc, pubhouses, books_pubhouses_assoc
WHERE authors.author_name LIKE '%escu' AND
authors.author_id = books_authors_assoc.author_id AND
books_authors_assoc.book_id = books.book_id AND
books.book_id = books_pubhouses_assoc.book_id AND
books_pubhouses_assoc.pubhouse_id = pubhouses.pubhouse_id
ORDER BY pubhouses.pubhouse_name DESC;

The above query does it's job, but the joining of those 5 tables is too slow (not to mention the duplicate results, needing DISTINCT or GROUP BY - more time needed).

Please show me the way to optimize that query.

Thank you.
cc

 

[guelphdad]

what indexes do you have?

 

[cristiancodreanu]

the above tables are just to illustrate the problem, the real DB being more large (and the tables have more columns).
The indexes are on the 'id' fields. Any advices?
Thanks.

 

[r937]

it could be slow because of this --

authors.author_name LIKE '%escu'

r937.com | rudy.ca

 

[cristiancodreanu]

@r937: this is one of the reasons for sure. But the main problem is the huge (even for only few records in the tables) cartesian product of those 5 tables.

 

[r937]

are there indexes on the foreign keys as well?

r937.com | rudy.ca

 

[cristiancodreanu]

yes

 

[r937]

and what about the duplicate results? could you perhaps consider collapsing one set or the other (authors or publishing houses) using GROUP_CONCAT?

and why is there a book_author column in the books table if you have an associative table?

r937.com | rudy.ca

 

[cristiancodreanu]

Thanks for pointing that, r937. book_author should not be there. However, this is not the real database I'm having problems with, this is meant to serve as a concise illustration of the problem.
Unfortunately GROUP_CONCAT does not help much, the join would still be huge (5 tables again) and I would further process the GROUP CONCAT in php (explode).

 

[r937]

change this --

SELECT books.book_title, books.book_author, authors.author_name, pubhouses.pubhouse_name
FROM books, authors, books_authors_assoc, pubhouses, books_pubhouses_assoc
WHERE authors.author_name LIKE '%escu' AND
authors.author_id = books_authors_assoc.author_id AND
books_authors_assoc.book_id = books.book_id AND
books.book_id = books_pubhouses_assoc.book_id AND
books_pubhouses_assoc.pubhouse_id = pubhouses.pubhouse_id
ORDER BY pubhouses.pubhouse_name DESC;


to this --

SELECT books.book_title
     , bookauthors.author_names
     , bookpubhouses.pubhouse_names
  FROM books
LEFT OUTER
  JOIN (
       SELECT books_authors_assoc.book_id
            , GROUP_CONCAT(authors.author_name)
                                as author_names
         FROM books_authors_assoc
       INNER
         JOIN authors
           ON authors.author_id 
            = books_authors_assoc.author_id
       GROUP
           BY books_authors_assoc.book_id
       ) as bookauthors 
    ON bookauthors.book_id = books.book_id
LEFT OUTER
  JOIN (
       SELECT books_pubhouses_assoc.book_id
            , GROUP_CONCAT(pubhouses.pubhouse_name)
                                  as pubhouse_names
         FROM books_pubhouses_assoc
       INNER
         JOIN pubhouses
           ON pubhouses.pubhouse_id 
            = books_pubhouses_assoc.pubhouse_id
       GROUP
           BY books_pubhouses_assoc.book_id
       ) as bookpubhouses 
    ON bookpubhouses.book_id = books.book_id

come on, just try it...

r937.com | rudy.ca

 

[cristiancodreanu]

thanks, I will try that too (I will have to add to it my WHERE condition though )

 

[r937]

add what to your WHERE condition?

r937.com | rudy.ca

 

[r937]

oh, wait, i know what you mean, you want this only for the selected authors

hang on a sec...

r937.com | rudy.ca

 

[r937]

SELECT books.book_title
     , bookauthors.author_names
     , bookpubhouses.pubhouse_names
  FROM [blue]authors as A1
INNER
  JOIN books_authors_assoc as BA1
    ON BA1.author_id = A1.author_id
INNER
  JOIN books
    ON books.book_id = BA1.book_id
INNER
  JOIN[/blue] (
       SELECT books_authors_assoc.book_id
            , GROUP_CONCAT(authors.author_name)
                                as author_names
         FROM books_authors_assoc
       INNER
         JOIN authors
           ON authors.author_id 
            = books_authors_assoc.author_id
       GROUP
           BY books_authors_assoc.book_id
       ) as bookauthors 
    ON bookauthors.book_id = books.book_id
LEFT OUTER
  JOIN (
       SELECT books_pubhouses_assoc.book_id
            , GROUP_CONCAT(pubhouses.pubhouse_name)
                                  as pubhouse_names
         FROM books_pubhouses_assoc
       INNER
         JOIN pubhouses
           ON pubhouses.pubhouse_id 
            = books_pubhouses_assoc.pubhouse_id
       GROUP
           BY books_pubhouses_assoc.book_id
       ) as bookpubhouses 
    ON bookpubhouses.book_id = books.book_id
 [blue]WHERE A1.author_name LIKE '%escu'[/blue]

r937.com | rudy.ca

 

[cristiancodreanu]

Tanks r937, I will try that!

 

[0ddball]

Did it work?

Don't forget to give stars if your thread was helpful!! Helps folks like me track down answers to problems so I don't have to ask stupid questions.


Yet another unchecked rambling brought to you by:
Oddball