How to improve the performance of this query

Databases
1

Project:

I am trying to create an on online search facility to dispense with the need to download a book shop’s daily XLS spreadsheet.

The spreadsheet has about 22,000 records in the following format:

Title:  Mozart For a Merry Christmas
Author: Various 
Ref:    463833 
Type:   CD
Baht:   150 

Title:  Angels, Arguments and a Furry Merry Christmas (Ally's World)
Author: McCombie, Karen 
Ref:    454408 
Type:   Children
Baht:   190 

Title:  Merry Christmas, Geronimo! (Geronimo Stilton #12)
Author: Stilton, Geronimo 
Ref:    478636 
Type:   Children
Baht:   90

### Online Demo

The spreadsheet is downloaded daily using CRON and converted into a MYSQL Database.

Currently the following books and SQL statement may be displayed using AJAX on

THIS PAGE .

Entering “thanks giving”, without quotes, will search the books table using this query:


    SELECT COUNT(*) AS `recNo`
    FROM `booksQQQ`
    WHERE
    CONCAT
    ( `author`,`ref`,`memo`,`baht`,`type`, `qqq`)
    LIKE '%thanks%'
    AND
    CONCAT
    ( `author`,`ref`,`memo`,`baht`,`type`, `qqq`)
    LIKE '%giving%' ;
    SELECT `author`,`ref`,`memo`,`baht`,`type`, `qqq`
    FROM `booksQQQ`
    WHERE
    CONCAT
    ( `author`,`ref`,`memo`,`baht`,`type`, `qqq`)
    LIKE '%thanks%'
    AND
    CONCAT
    ( `author`,`ref`,`memo`,`baht`,`type`, `qqq`)
    LIKE '%giving%'
    LIMIT 0, 42;

####And produce the following book:

Title: Thanksgiving on Thursday (Magic Tree House)
by: Osborne, Mary Pope 
Baht: 70 
Ref: #469961 
Type: Children

### Indices The following two indices have been set on the books table: 
$sql = 'ALTER TABLE `' .$tableName .'` ADD PRIMARY KEY(`id`);';
$sql = 'ALTER TABLE `' .$tableName .'` ADD UNIQUE KEY `ref` (`ref`)';

I am curious to know:

  1. if the search could be improved by adding a composite index
  2. or adding indexes for every searched field (6)
  3. or even another a completely new method of searching.
2

1/ if the search could be improved by adding a composite index

no, because of the CONCAT

2/ or adding indexes for every searched field (6)

no

3/ or even another a completely new method of searching.

unlikely

1 Like
3

If you want to use MySQL for this, while 22k rows are not really much I would strongly recommend considering using a different approach.

Take a look on fulltext search, in theory it should improve your query times.
https://dev.mysql.com/doc/refman/5.7/en/fulltext-natural-language.html

If you really want to speed it up, though not sure if this is worth it with only 22k rows, you can look into Elasticsearch

1 Like
4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.