5
\$\begingroup\$

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?

\$\endgroup\$
9
  • 1
    \$\begingroup\$ For 5 million records...using LIKE...? 60 seconds doesn't sound all that outrageous. \$\endgroup\$
    – cHao
    Jul 19 '14 at 17:39
  • 2
    \$\begingroup\$ 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. \$\endgroup\$
    – janos
    Jul 19 '14 at 17:41
  • \$\begingroup\$ Thanks for including some schema and EXPLAIN information with your question. \$\endgroup\$ Jul 19 '14 at 21:24
  • \$\begingroup\$ @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 :( \$\endgroup\$ Jul 19 '14 at 21:48
  • \$\begingroup\$ @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 :) ) \$\endgroup\$ Jul 19 '14 at 21:51
2
\$\begingroup\$

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

\$\endgroup\$
5
  • \$\begingroup\$ 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) \$\endgroup\$ Jul 19 '14 at 21:56
  • \$\begingroup\$ But it doesn't give you the number of books with that title. What happens if a book has multiple authors? \$\endgroup\$ Jul 20 '14 at 4:25
  • \$\begingroup\$ 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 \$\endgroup\$ Jul 20 '14 at 7:55
  • \$\begingroup\$ 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 …)). \$\endgroup\$ Jul 20 '14 at 8:04
  • \$\begingroup\$ I think this would improve the query speed. I'm going to rewrite my query to test this. \$\endgroup\$ Jul 20 '14 at 8:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.