Joining 3 tables, query not responding

Question

Im having problem trying to join 3 tables, every time I run the query on my server I just get a huge pause which requires manual termination.

here is the table strictures

books   : isbn(PK)
books_a : id(PK), isbn, price, condition
books_l : id(PK), isbn, price, condition

here is the query

SELECT 
   b.isbn, 
   a.price       AS a_price, 
   a.condition   AS a_condition, 
   l.price       AS l_price, 
   l.condition   AS l_condition 

FROM 
   books b  

   LEFT JOIN a_books a ON b.isbn  = a.isbn 
   LEFT JOIN l_books l ON b.isbn  = l.isbn

What could be the problem with my query? (note I do have over 6,000 records in each table)

@mk_89 do you really define it as `PK` and `FK` on the tables or just *verbal* assignment. Can you run this query? `DESCRIBE books;`, `DESCRIBE books_l;`,`DESCRIBE books_a;` and post the results here? — John Woo, Sep 27 '12 at 06:09
@mk_89 then you should define keys for `books_l` and `books_a` :) — John Woo, Sep 27 '12 at 06:13
@JohnWoo the primary keys for those tables are id, since a_books and l_books may have multiple books with the same isbn it would not be possible to assign the isbn field with a key — mk_89, Sep 27 '12 at 06:14
@mk_89 then define atleast `non-unique` index on those columns since they are being used in joining your tables. — John Woo, Sep 27 '12 at 06:18
@JohnWoo well I indexed the isbn fields and everything is running. thanks — mk_89, Sep 27 '12 at 06:24

score 3 · Accepted Answer · edited May 23 '17 at 11:56

3

I bet that one or some of these columns are not indexed. a_books.isbn, books.isbn, l_books.isbn

try running this statements,

ALTER table a_books ADD INDEX IDX_abk (isbn);
ALTER table books ADD INDEX IDX_bk (isbn);
ALTER table l_books ADD INDEX IDX_lbk (isbn);

Read this article: How does database indexing work?

edited May 23 '17 at 11:56

Community

1
1

answered Sep 27 '12 at 06:00

John Woo

258,903
69
498
492

Ah I see what you mean, yes both tables a & l may not have the isbn number – mk_89 Sep 27 '12 at 06:02
@mk_89 the existence of values for `isbn` on other tables does not matter at all. What matters most is the `index` you have defined in your tables. – John Woo Sep 27 '12 at 06:05

score 1 · Answer 2 · answered Sep 27 '12 at 05:58

1

The problem can be if isbn does not have an index.

answered Sep 27 '12 at 05:58

Alex

11,451
6
37
52

What exactly do you mean by that? – mk_89 Sep 27 '12 at 05:59

score 1 · Answer 3 · answered Sep 27 '12 at 06:06

1

Clearly index is the culprit with > 6K records in the table.

answered Sep 27 '12 at 06:06

gdanton

310
2
13

score 1 · Answer 4 · answered Sep 27 '12 at 06:24

1

Another reason could be that after joining first table it will be huge scan for the second join. So it will return you a lot of rows.

answered Sep 27 '12 at 06:24

Alex

11,451
6
37
52

Joining 3 tables, query not responding

4 Answers4