Return to Revisions

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?