MySQL query usually fast, sometimes very slow (when run on my website)

Question

I have a query on my MySQL database that is used to return products (on an e-commerce site) after the user performs a search in a free-form text box.

Until recently, user searches have been running fast. However, in the last few days, the searches are periodically very slow. This occurs for about 3 or 4 hours (spread randomly throughout the day) each day.

I had thought that there was a problem with my server. But now I have moved to another server, and the same thing still happens.

I suspect that the query I use is quite inefficient, and maybe this is the cause. But I don't understand why usually the query can run fast, and at other times be very slow. I would assume that an inefficient query would always be slow.

The query runs on two tables. If the search is for "blue jeans", then the query would look like this:

SELECT I.itemURL, I.itemTitle, I.itemPrice, I.itemReduced, I.itemFileName, I.itemBrand, I.itemStore, I.itemID, I.itemColour, I.itemSizes, I.itemBrandEn
FROM Item AS I, Search AS S
    WHERE I.itemID = S.itemID
        AND (S.searchStringTEXT LIKE '% blue %' OR S.searchStringTEXT LIKE 'blue %' OR S.searchStringTEXT LIKE '% blue')
            AND (S.searchStringTEXT LIKE '% jeans %' OR S.searchStringTEXT LIKE 'jeans %' OR S.searchStringTEXT LIKE '% jeans')

Item is the table containing all products on the site. It has around 100,000 rows.

Search is a table containing product ids, and the tags associated to each product id. The tags are in the column "searchStringTEXT", and are separated by spaces. E.g., an entry in this column may be something like "jeans blue calvin klein small".

The search above will find all items that have both the tag "jeans" and "blue" attached.

In theory, Search should have the same number of rows as Item; but, due to a problem that I haven't fixed yet, it has about 500 fewer rows, so these items are effectively excluded from searches.

The create table details for both tables are as follows:

CREATE TABLE `Search` (
  `itemID` int(11) NOT NULL,
  `searchStringTEXT` varchar(255) DEFAULT NULL,
  `searchStringVARCHAR` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`itemID`),
  KEY `indexSearch_837` (`itemID`) USING BTREE,
  KEY `indexSearch_837_text` (`searchStringTEXT`)
) ENGINE=InnoDB DEFAULT CHARSET=latin5

and

CREATE TABLE `Item_8372` (
  `itemID` int(11) NOT NULL AUTO_INCREMENT,
  `itemURL` varchar(2000) DEFAULT NULL,
  `itemTitle` varchar(500) DEFAULT NULL,
  `itemFileName` varchar(200) DEFAULT NULL,
  `itemPictureURL` varchar(2000) DEFAULT NULL,
  `itemReduced` int(11) DEFAULT NULL,
  `itemPrice` int(11) DEFAULT NULL,
  `itemStore` varchar(500) DEFAULT NULL,
  `itemBrand` varchar(500) CHARACTER SET latin1 DEFAULT NULL,
  `itemShopCat` varchar(500) DEFAULT NULL,
  `itemTimestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `itemCat` varchar(200) DEFAULT NULL,
  `itemSubCat` varchar(200) DEFAULT NULL,
  `itemSubSubCat` varchar(200) DEFAULT NULL,
  `itemSubSubSubCat` varchar(200) DEFAULT NULL,
  `itemColour` varchar(200) DEFAULT NULL,
  `itemSizes` varchar(200) DEFAULT NULL,
  `itemBrandEn` varchar(500) DEFAULT NULL,
  `itemReduction` float DEFAULT NULL,
  `ItemPopularity` int(6) DEFAULT NULL,
  PRIMARY KEY (`itemID`),
  KEY `indexItem_8372_ts` (`itemTimestamp`) USING BTREE,
  KEY `indexItem_8372_pop` (`ItemPopularity`),
  KEY `indexItem_8372_red` (`itemReduction`),
  KEY `indexItem_8372_price` (`itemReduced`)
) ENGINE=InnoDB AUTO_INCREMENT=970846 DEFAULT CHARSET=latin5

In the title of the question I say "(when run on my website)", because I find that the query's speed is consistent when run locally. But maybe this is just because I haven't tested it as much locally.

I'm thinking of changing the Search table so it's a MyISAM table, and then I can use a full text search instead of "LIKE". But I'd still like to figure out why I am experiencing what I am experiencing with the current setup.

Any ideas/suggestions much appreciated, Andrew

Edit:

Here is the EXPLAIN result for the SELECT statement:

id  select_type table   type    possible_keys   key key_len ref rows         Extra
1   SIMPLE  I   ALL PRIMARY NULL    NULL    NULL    81558   
1   SIMPLE  S   eq_ref  PRIMARY,indexSearch_837,indexSearch_837_text    PRIMARY 4       I.itemID    1   Using where

Please show us an `EXPLAIN` of your select statement. – Kermit Dec 11 '12 at 16:28 — Kermit, Dec 11 '12 at 16:28
@njk, I have added it at the bottom – Andrew Dec 11 '12 at 16:42 — Andrew, Dec 11 '12 at 16:42

score 1 · Accepted Answer · answered Dec 11 '12 at 16:29

Using LIKE will result in slow queries, especially the amount of times you have it. This is because LIKE scans all the rows in the table, which even with a few hundred can cause a problem (add to that the number of times you used LIKE with different variations of text to match with).

It only takes a few people simultaneously loading a page that runs this query to really slow your site down. Furthermore, the query may be running multiple times on your server in background threads from earlier, causing the longer-term slowdowns you're seeing.

If you're going to be performing text searches regularly, consider a RAM-based indexing solution for your search such as Sphinx. You could perform the text search there (it will be very fast compared to MySQL) and then retrieve the needed rows from the MySQL tables after.

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

You might want to try using REGEXP (reference). It's also better practice to stop using implicit joins.

SELECT I.itemurl, 
       I.itemtitle, 
       I.itemprice, 
       I.itemreduced, 
       I.itemfilename, 
       I.itembrand, 
       I.itemstore, 
       I.itemid, 
       I.itemcolour, 
       I.itemsizes, 
       I.itembranden 
FROM   item AS I 
       INNER JOIN search AS S 
               ON I.itemid = S.itemid 
WHERE  S.searchstringtext REGEXP ' blue | blue|blue ' 
       AND S.searchstringtext REGEXP ' jeans | jeans|jeans '

edited May 23 '17 at 11:48

Community

1
1

answered Dec 11 '12 at 16:46

Kermit

33,827
13
85
121

Thanks. I will try this. Also, what do you think about using the Full Text search feature on S.searchstringtext? (But to do this I have to convert Search into a MyISAM table, and all other tables are InnoDB. Not sure if this is a problem). – Andrew Dec 11 '12 at 16:50
@Andrew You'll have to do benchmarking to determine what will be appropriate. Obviously, moving to MyISAM means you'll lose relational integrity unless you use triggers. – Kermit Dec 11 '12 at 16:52

MySQL query usually fast, sometimes very slow (when run on my website)

2 Answers2