Library database search with multiple JOINs is too slow

Question

I have a MySQL query which runs very slowly. I've rewritten it many times but no improvements yet.

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 5 million records) takes 60 secs!

What am I doing wrong here?

Answer 1

Rather than address performance, I'd first like to raise some skepticism about the correctness of the query.

I don't think that PhysicalInfo A LEFT JOIN BibliographicInfo is appropriate. What that LEFT JOIN means is that it's OK to include a PhysicalInfo record that has no corresponding BibliographicInfo record. It is therefore possible for a selected BibliographicInfo.title to be NULL.

I'm also puzzled by COUNT(*). What exactly is it that you are trying to count? The number of BookAuthors times the number of BooksLocations times the number of Publishers times the number of Series that each book title appears in (except that each zero counts as a factor of one)? I'm not even sure that such a count has a reasonable practical interpretation. Furthermore, you used an aggregate function COUNT(*) without any GROUP BY, which is forbidden in standard SQL. (MySQL is unreasonably lenient about these broken queries unless you configure the server to be strict, using ONLY_FULL_GROUP_BY.)