Please help me optimize a mysql search query

Question

I have a query in MySql (5.1) InnoDB that searches in a table with parts. The table with parts contains about 500 000 rows. The search also joins two other tables tblcategory and tblheadcategory. I have a lot of users using this query and it makes my server almost crasch with the heavy load.

I know that a good way would be to use full-text search for this, and I hope we can change this to use it in the future. But as that is not possible with InnoDB I need a "quick" optimization to get it running for now. How should I optimize this and setup Index and other things to get this query to run as good as possible?

This is the query:

SELECT tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory

FROM tblpart

INNER JOIN tblcategory ON tblpart.categoryid = tblcategory.categoryid
INNER JOIN tblheadcategory ON tblcategory.headcategoryid = tblheadcategory.headcategoryid

WHERE (tblpart.title LIKE '%bmw%' OR tblpart.description LIKE '%bmw%' OR tblpart.brand LIKE '%bmw%')

ORDER BY

tblpart.title='bmw' DESC,
tblcategory.category LIKE '%bmw%' DESC

LIMIT 50;

The tables:

CREATE TABLE `tblpart` (
    `partid` int(10) NOT NULL auto_increment,
    `userid` int(11) default '1',
    `categoryid` int(10) default '1',
    `title` varchar(100) default NULL,
    `brand` varchar(100) default NULL,
    `description` varchar(100) default NULL,
    PRIMARY KEY  (`partid`),
    KEY `userid` (`userid`),
    KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=534007 DEFAULT CHARSET=utf8;

CREATE TABLE `tblcategory` (
    `categoryid` int(10) NOT NULL auto_increment,
    `category` varchar(255) default NULL,
    `headcategoryid` int(10) default NULL,
      PRIMARY KEY  (`categoryid`)
) ENGINE=InnoDB AUTO_INCREMENT=1261 DEFAULT CHARSET=utf8;

CREATE TABLE `tblheadcategory` (
    `headcategoryid` int(10) NOT NULL auto_increment,
    `headcategory` varchar(255) default NULL,
    PRIMARY KEY  (`headcategoryid`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

EXPLAIN gives following: (Sorry, I can't figure out how to format it right)

id   select_type   table            type    possible_keys   key      key_len  ref                         rows        extra
1    SIMPLE        tblpart          ALL     NULL            NULL     NULL     NULL                        522905      Using where; Using temporary; Using filesort
1    SIMPLE        tblcategory      eq_ref  PRIMARY         PRIMARY  4        tblpart.categoryid          1
1    SIMPLE        tblheadcategory  eq_ref  PRIMARY         PRIMARY  4        tblcategory.headcategoryid  1

UPDATE

From the suggestions I tried a FULLTEXT solution:

The new MyISAM table:

CREATE TABLE `tblpart_search` (
    `partid` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    `brand` varchar(100) DEFAULT NULL,
    `description` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`partid`),
    FULLTEXT KEY `all` (`title`,`brand`,`description`)
) ENGINE=MyISAM AUTO_INCREMENT=359596 DEFAULT CHARSET=utf8;

Triggers:

DELIMITER ;;
CREATE TRIGGER `tblpart_insert_trigger` AFTER INSERT ON `tblpart` 
FOR EACH ROW INSERT INTO tblpart_search VALUES(NEW.partid,NEW.title,NEW.brand,NEW.description);;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_update_trigger` AFTER UPDATE ON `tblpart` 
FOR EACH ROW UPDATE tblpart_search SET tblpart_search.title=NEW.title,tblpart_search.brand=NEW.brand,tblpart_search.description=NEW.description WHERE tblpart_search.partid=NEW.partid;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_delete_trigger` AFTER DELETE ON `tblpart` 
FOR EACH ROW DELETE FROM tblpart_search WHERE tblpart_search.partid=OLD.partid;;
DELIMITER ;

The new query:

SELECT tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory

FROM tblpart_search
INNER JOIN tblpart ON tblpart_search.partid = tblpart.partid
INNER JOIN tblcategory ON tblpart.categoryid = tblcategory.categoryid
INNER JOIN tblheadcategory ON tblcategory.headcategoryid = tblheadcategory.headcategoryid

WHERE MATCH (tblpart_search.title, tblpart_search.brand, tblpart_search.description) AGAINST ('bmw,car')
LIMIT 50;

Can you do an `EXPLAIN` on the query, so we can see the existing plan. Even though you have provided enough information to guess, it's always good to see. — Orbling, Jan 20 '11 at 15:33
`%%` searches are always going to be slow without a full text search. I usually setup a MyISAM table with copies of the search fields, with `FULLTEXT` indexes setup to link in. — Orbling, Jan 20 '11 at 15:35
@Orbling: with leading wildcards they are going to be slow even with a `FULLTEXT` search. — Quassnoi, Jan 20 '11 at 15:43
@Orbling: I tried to add the EXPLAIN part but I couldn't get it formatted right in Stackoverflow.. — Martin, Jan 20 '11 at 16:14
Formatted it for you, the thing that busts it up is the tabs, if you convert to spaces it sits alright. — Orbling, Jan 20 '11 at 16:20

Quassnoi · Accepted Answer · 2011-01-21T10:30:34.293

2

You cannot really optimize a query with leading wildcards (even with FULLTEXT searches).

The only thing you can do here is to split the query in three (on client side):

SELECT  tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory
FROM    tblpart
INNER JOIN
        tblcategory
ON      tblpart.categoryid = tblcategory.categoryid
INNER JOIN
        tblheadcategory
ON      tblcategory.headcategoryid = tblheadcategory.headcategoryid
WHERE   tblpart.title = 'bmw'
ORDER BY
        tblcategory.category LIKE '%bmw%' DESC
LIMIT 50

SELECT  tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory
FROM    tblpart
INNER JOIN
        tblcategory
ON      tblpart.categoryid = tblcategory.categoryid
INNER JOIN
        tblheadcategory
ON      tblcategory.headcategoryid = tblheadcategory.headcategoryid
WHERE   tblpart.title <> 'bmw'
        AND  (tblpart.title LIKE '%bmw%' OR tblpart.description LIKE '%bmw%' OR tblpart.brand LIKE '%bmw%')
        AND tblcategory.category LIKE '%bmw%'
LIMIT N

SELECT  tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory
FROM    tblpart
INNER JOIN
        tblcategory
ON      tblpart.categoryid = tblcategory.categoryid
INNER JOIN
        tblheadcategory
ON      tblcategory.headcategoryid = tblheadcategory.headcategoryid
WHERE   tblpart.title <> 'bmw'
        AND  (tblpart.title LIKE '%bmw%' OR tblpart.description LIKE '%bmw%' OR tblpart.brand LIKE '%bmw%')
        AND tblcategory.category NOT LIKE '%bmw%'
LIMIT N

and replace N in the last queries with 50 - records, where records is the number of records returned by the previous queries

The first query can be served with an index on title.

Update:

A FULLTEXT search can be implemented like this:

CREATE TABLE `tblpart_search` (
    `partid` int(11) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    `brand` varchar(100) DEFAULT NULL,
    `description` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`partid`),
    FULLTEXT KEY `all` (`title`,`brand`,`description`)
) ENGINE=MyISAM AUTO_INCREMENT=359596 DEFAULT CHARSET=utf8;

Triggers:

DELIMITER ;;
CREATE TRIGGER `tblpart_insert_trigger` AFTER INSERT ON `tblpart` 
FOR EACH ROW INSERT INTO tblpart_search VALUES(NEW.partid,NEW.title,NEW.brand,NEW.description);;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_update_trigger` AFTER UPDATE ON `tblpart` 
FOR EACH ROW UPDATE tblpart_search SET tblpart_search.title=NEW.title,tblpart_search.brand=NEW.brand,tblpart_search.description=NEW.description WHERE tblpart_search.partid=NEW.partid;;
DELIMITER ;

DELIMITER ;;
CREATE TRIGGER `tblpart_delete_trigger` AFTER DELETE ON `tblpart` 
FOR EACH ROW DELETE FROM tblpart_search WHERE tblpart_search.partid=OLD.partid;;
DELIMITER ;

The new query:

SELECT tblpart.partid,tblpart.title,tblcategory.category,tblheadcategory.headcategory

FROM tblpart_search
INNER JOIN tblpart ON tblpart_search.partid = tblpart.partid
INNER JOIN tblcategory ON tblpart.categoryid = tblcategory.categoryid
INNER JOIN tblheadcategory ON tblcategory.headcategoryid = tblheadcategory.headcategoryid

WHERE MATCH (tblpart_search.title, tblpart_search.brand, tblpart_search.description) AGAINST ('+bmw +car' IN BOOLEAN MODE)
LIMIT 50;

Set ft_min_word_len to 3 or less so that it could index the 3-character words like 'BMW' and 'CAR'.

edited Jan 21 '11 at 10:30

answered Jan 20 '11 at 15:40

Quassnoi

413,100
91
616
614

The `FULLTEXT` searches match on words and to an extent phrases, rather than worrying about wildcards, so is considerably faster than using the LIKE lexiographic matching. – Orbling Jan 20 '11 at 15:43
@Orbling: sure, but the logic is slightly different (though it might not be what the op wants). A `FULLTEXT` query cannot match `Hertfordshire` when searching for `ford`. – Quassnoi Jan 20 '11 at 15:52
@Quassnoi: Quite right, it is not geared that way. If you need arbitrary matching within words/strings, then the pattern matching is the only way and it'll always be slow. – Orbling Jan 20 '11 at 16:03
I think FULLTEXT is enough if that will give me results with title "car bmw", "bmw car" when I search for "bmw car". Do you recommend setting up an extra table using myisam that have partid,title so that I can do a faster FULLTEXT search in that? – Martin Jan 20 '11 at 16:12
@Martin: definitely do a `FULLTEXT` table. If you don't need infix matches, it will speed up the queries greatly. – Quassnoi Jan 20 '11 at 16:14
@Quassnoi, I'm not really good with MySql, how hard is it to set this up? – Martin Jan 20 '11 at 16:15
1

@Martin: `CREATE TABLE ft_tlbpart (id INT NOT NULL PRIMARY KEY, title TEXT NOT NULL, FULLTEXT KEY (title)) ENGINE=MyISAM; INSERT INTO ft_tblpart SELECT id, title FROM tblpart;`. That hard. But note that you will have to do inserts, updates and deletes to both tables from now on (solvable with a trigger). – Quassnoi Jan 20 '11 at 16:18
1

@Martin: It will only match 3-letter words in MySQL if you adjust the server setting for minimum word length, which defaults to 4, a major gotcha people often are not aware of. – Orbling Jan 20 '11 at 16:32
@Quassnoi: With regard to the INSERTs/DELETEs, triggers come in handy there. – Orbling Jan 20 '11 at 16:33
@Quassnoi: Sorry, that was a typo, yes 4, if it was 3 I would not have brought it up, lol! – Orbling Jan 20 '11 at 16:36
@Orbling, @Quassnoi: I added the FULLTEXT solution that you recommended to my question. What do you think? Was that what you had in mind? Can you see any problems with it? Thanks a lot for your help! – Martin Jan 20 '11 at 20:10
@Martin: Great, though I think your INSERT and UPDATE triggers will need to add in the information for the `brand` and `description` fields too. Also, I can not recall the exact specification for `MATCH AGAINST`, but you may get better results with a space instead of a `,` separator for the words. Remember to ensure the server `ft_min_word_length` is set to 3. http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html – Orbling Jan 20 '11 at 20:22
@Martin: yes, that's how it should be, just adjust your `ft_min_word_len`, rebuild the index after that and use a boolean search: `MATCH (title, brand, description) AGAINST ('+bmw +car' IN BOOLEAN MODE)` – Quassnoi Jan 20 '11 at 21:07
Thanks @Orbling and Quassnoi! I would like my search to work something like this (I understand that I can only search full words though): (title LIKE '%bmw%' AND title LIKE '%car%') OR (brand LIKE '%bmw%' AND brand LIKE '%car%') OR (description LIKE '%bmw%' AND description LIKE '%car%') is ('+bmw +car' IN BOOLEAN MODE) the way to go then? – Martin Jan 20 '11 at 21:19
@Martin: Using the approach described by @Quassnoi, as you have stated, you should get something like that. But bare in mind `FULLTEXT` matching returns results quite differently to the regular `LIKE` approach. You will probably want to `ORDER` the results by the `MATCH` value, descending order, so the best matches come out first. – Orbling Jan 20 '11 at 22:00
I've not implemented this and my server is much happier now :) Thanks! Would be great if any of you could copy my solution to a new answer so I can mark it as the correct answer, and give you the points, is that possible? – Martin Jan 21 '11 at 09:21

score 0 · Answer 2 · answered Jan 20 '11 at 15:35

0

Index the fields used in your where clause. I am not sure about having "tblpart.title='bmw' DESC, tblcategory.category LIKE '%bmw%' DESC" as I've only done things like "index the fields used in your where clause. I am not sure about tblpart.title DESC, tblcategory.category DESC"

answered Jan 20 '11 at 15:35

IEnumerator

2,960
5
31
33

Thanks for your answer! Will index help even if I'm using wildcards? Can the sort part of the query slow it down? – Martin Jan 20 '11 at 16:02
yes - definitely. you want to make sure that the fields in the where and sort clause are indexed properly. Look also into full-text search/indexing performance also. – IEnumerator Jan 25 '11 at 13:47

score 0 · Answer 3 · answered Jan 20 '11 at 15:53

I think the code tblpart.title='bmw' DESC should be changed to tblpart.title LIKE '%bmw%' DESC
Create a new table which will work as an index for text search where you can store the search term input by the user and also the common search term related to the tblpart.title and the partid. Now whenever the user hits search then you first search this table and if the search term matches the query with that partid which is much faster.

Please help me optimize a mysql search query

UPDATE

3 Answers3