My JOIN query on multiple tables is very slow

Question

My current query for advanced search takes about 60 secs to complete. Any suggestions for improving this? Database: MySQL Webserver: Apache - PHP (PDO)

Current code structure:

SELECT  B.title, COUNT(*) AS NUM 
FROM library.PhysicalInfo A 
LEFT JOIN library.BibliographicInfo B 
    ON A.BookID = B.BibliographicInfoID 
LEFT JOIN library.authors C 
    ON B.BibliographicInfoID = C.BookID 
LEFT JOIN library.BookAuthors D 
    ON C.BookAuthorID = D.PersonID 
LEFT JOIN library.series E 
    ON B.BibliographicInfoID = E.BookID 
LEFT JOIN library.BooksLocation F 
    ON A.location = F.BookLocationID 
LEFT JOIN library.PublishStatement G 
    ON B.BibliographicInfoId=G.BookID 
LEFT JOIN library.publisher H 
    ON G.PublisherID = H.PublisherID 
WHERE (     B.title  LIKE '%سلام%' OR 
            D.name LIKE '%سلام%' OR 
            F.location LIKE '%سلام%' OR 
            G.place  LIKE '%سلام%' OR 
            G.year LIKE '%سلام%' OR 
            H.name LIKE '%سلام%' ) 
LIMIT 0, 30

I've created indexes for all the fields which are used in join comparisons and WHERE statements:

CREATE INDEX PersonID ON library.BookAuthors(PersonID);
....

My Table Structures are:

mysql> DESCRIBE PhysicalInfo;

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| PhysicalInfoID | int(11)      | NO   | PRI | NULL    | auto_increment |
| volume         | varchar(10)  | YES  |     | NULL    |                |
| section        | varchar(100) | YES  |     | NULL    |                |
| year           | varchar(10)  | YES  |     | NULL    |                |
| RegisterNo     | varchar(20)  | YES  |     | NULL    |                |
| barcode        | varchar(45)  | YES  | MUL | NULL    |                |
| location       | int(11)      | YES  | MUL | NULL    |                |
| BookID         | int(11)      | YES  | MUL | NULL    |                |
| version        | varchar(10)  | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+

mysql> DESCRIBE BibliographicInfo;

+------------------------+---------------+------+-----+---------+----------------+
| Field                  | Type          | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+----------------+
| BibliographicInfoID    | int(11)       | NO   | PRI | NULL    | auto_increment |
| LCNO                   | varchar(45)   | YES  |     | NULL    | |
| DeviNo                 | varchar(45)   | YES  |     | NULL    | |
| title                  | varchar(200)  | YES  | MUL | NULL    | |
| ISBN                   | varchar(20)   | YES  | MUL | NULL    | |
| language               | int(11)       | YES  |     | NULL    | |
| IsReference            | smallint(6)   | YES  |     | NULL    | |
+------------------------+---------------+------+-----+---------+----------------+

EXPLAIN EXTENDED Result:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 348390
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: B
         type: eq_ref
possible_keys: PRIMARY,BibliographicInfoID
          key: PRIMARY
      key_len: 4
          ref: library.A.BookID
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: C
         type: ref
possible_keys: BookID_idx,BookID
          key: BookID_idx
      key_len: 5
          ref: library.B.BibliographicInfoID
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: D
         type: eq_ref
possible_keys: PRIMARY,PersonID
          key: PRIMARY
      key_len: 4
          ref: library.C.BookAuthorID
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 5. row ***************************
           id: 1
  select_type: SIMPLE
        table: E
         type: ref
possible_keys: BookID
          key: BookID
      key_len: 5
          ref: library.B.BibliographicInfoID
         rows: 2
     filtered: 100.00
        Extra: Using index
*************************** 6. row ***************************
           id: 1
  select_type: SIMPLE
        table: F
         type: eq_ref
possible_keys: PRIMARY,BookLocationID
          key: PRIMARY
      key_len: 4
          ref: library.A.location
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 7. row ***************************
           id: 1
  select_type: SIMPLE
        table: G
         type: ref
possible_keys: book_idx,BookID
          key: book_idx
      key_len: 5
          ref: library.B.BibliographicInfoID
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 8. row ***************************
           id: 1
  select_type: SIMPLE
        table: H
         type: eq_ref
possible_keys: PRIMARY,PublisherID
          key: PRIMARY
      key_len: 4
          ref: library.G.PublisherID
         rows: 1
     filtered: 100.00
        Extra: Using where
8 rows in set, 1 warning (0.00 sec)

my current query (on about 5million records) takes 60 secs! So my QUESTION is:

What Am I Doing Wrong Here?
Why Does The First Row in EXPLAIN EXTENDED always has type=All? as far as I know, this is worst type and shouldn't be there because I've created INDEXes for BookID and PRIMARY KEY of that table.

I can't beleive this question is still closed! I have no idea how to be more specific about my question? This is a community, not a military base... — Sina Bizbone, Jul 06 '14 at 00:37
"%x" cannot use an index so it's always going to struggle. If it helps, "x%" CAN use an index, so it's much faster. Maybe take a look at fulltext searches. — Strawberry, Jul 18 '14 at 10:51
@Strawberry: Thanks, I know that using a wildcard before a keyword, would cause the optimizer to ignore the Index, but it still should not return that many rows on the first row of EXPLAIN EXTENDED. I've created indexes for columns such as BookID, PhysicalInfoID,... and not for varchar columns (those used in LIKE %%). — Sina Bizbone, Jul 18 '14 at 11:02
OK - but you mentioned that you had "created indexes for all the fields which are used in [...] WHERE statements", so I thought I'd just point out that these are redundant. — Strawberry, Jul 18 '14 at 11:12
@Strawberry: yes I've created for example an index on Book Titles but It would be used in GROUP BY operations not LIKE %%. Thanks fornoting that though :) — Sina Bizbone, Jul 18 '14 at 11:17
So you have a compound index on (`bookid`,`location`), right? `barcode` shouldn't be part of that. — Strawberry, Jul 18 '14 at 11:30
@Strawberry :I have created separate indexes on bookid and location. I'd like to know the difference. What does it mean? — Sina Bizbone, Jul 18 '14 at 14:08

score 2 · Answer 1 · edited May 23 '17 at 12:05

2

The first thing that I think about is INDEXES. If you choose them wisely it can improve the performance significantly. See here.
Sometimes you should consider flattening your tables. This way you break some relational database rules of normalization but you gain speed by performing less "Joins".
Yet another way I sometimes use: if one of the table hasn't many values, you can get it in separate query, moving "JOIN" field to the key, (caching the array) and perform JOIN after the query execution when fetching the records in php(or other language) code
And another approach is to perform some job on time basis that would prepare the data and insert it to another table, ready for fetching(not possible in many use cases due to delay)

And you always can store the values in cache.

edited May 23 '17 at 12:05

Community

1
1

answered Jun 08 '14 at 11:13

lvil

4,326
9
48
76

+1 ... with the correct indexes, `JOIN` operations can be *very* fast. Until very recent versions of MySQL, they were almost guaranteed to be faster than correlated subqueries. – O. Jones Jun 08 '14 at 11:25
@lvil: Could you give more info or a link on your third advice (key ...)? – Sina Bizbone Jun 08 '14 at 11:38
Unuseful example: you have a students table (id, name, degreeID), and degreeTable(degreeID, degreeName). First you fetch all from degreeTable, then convert it to array(degreeID=>degreeName). If you want to show a list of students, for each student you go to the array[degreeID]. – lvil Jun 08 '14 at 11:46
' you gain speed by performing less [sic] "Joins" ' I remain sceptical. – Strawberry Jul 18 '14 at 10:53

score 0 · Answer 2 · answered Jun 08 '14 at 11:05

0

If you need to select data from multiple tables, you have no choice other than to join them in a query.

A query with multiple nesting levels sounds like it might be over-complex, however I would suggest that you are the person creating the solution for the business and if you create a complex query that the Data Centre Admins don't approve of then you should be able to take it to them and discuss a better way. You may find that there isn't a better way than the solution you have provided.

The bottom line is, I don't believe that someone should be able to force such restrictions on you without offering help to meet those restrictions.

answered Jun 08 '14 at 11:05

Slade

2,311
3
21
25

Unfortunately, they do force such restriction! :( but my current query takes about 63 seconds to run on the server and this is really terrible! – Sina Bizbone Jun 08 '14 at 11:37
I would suggest that your query could be made more efficient by in different ways rather than trying to remove joins. I would first check that you have appropriate indexes and then you would need to consider altering the query but you would need to post it and explain a bit about the database if you wanted help with it. – Slade Jun 08 '14 at 11:40

My JOIN query on multiple tables is very slow

2 Answers2