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?

• cHao: For 5 million records...using LIKE...? 60 seconds doesn't sound all that outrageous.
• janos: I don't think indexes can help you much when you're doing LIKE '%something%'. Do you really need such searches? If LIKE 'something%' is enough that might help. It would be best eliminate all the LIKE if you can.
• 200_success: Thanks for including some schema and EXPLAIN information with your question.
• Sina Bizbone: @cHao: But current search engine (based on SQL Server 2000) does that in a few seconds (might be even lower since I'm looking from the client side). In a book search panel, this is outrageous :(
• Sina Bizbone: @janos: Yes, exactly, using a wildcard in the start of a string would cause all indexes on that column to become unuseful. But it doesn't seem to be any other way to implement a simple search box for users. (I obviously cannot ask them to enter the first few characters of their desired book :) )

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

• Sina Bizbone: I AM TERRIBLY Sorry! The "GROUP BY" was unintentionally deleted while I was formatting my question. Anyhow, The "SELECT" part doesn't seem to have much effect on speed. I used SELECT * / SELECT 'col_name' / , etc. "GROUP BY" is because I want to group my search results by title and COUNT(*) to give me the number of books in that title (It actually works fine, but slow)
• 200_success: But it doesn't give you the number of books with that title. What happens if a book has multiple authors?
• Sina Bizbone: It's a tough question... I think you're right and there's a call for a DISTINCT somewhere (maybe on bookIDs). I hate logical micro flaws in queries... thanks for noting
• 200_success: No. Using DISTINCT to deduplicate is inefficient and should be avoided. Instead, formulate your JOINs to produce the correct number of rows. (Don't JOIN if what you really mean is ... WHERE EXISTS (SELECT ...)).
• Sina Bizbone: I think this would improve the query speed. I'm going to rewrite my query to test this.