Library database search with multiple JOINs is too slow

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 | |

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.)

StackExchange Code Review Q#57464, answer score: 2