1

I am doing a complex full text search on a table with 130k+ records. It takes .5 seconds to complete. I was hoping it would be a bit faster.

Is there anything I can do to make it faster?

Below is the query along with explain + relevant tables

Query:

SELECT `phppos_items`.*, `phppos_categories`.`name` as category, `phppos_location_items`.`quantity` as quantity, `phppos_location_items`.`reorder_level` as location_reorder_level, `phppos_location_items`.`cost_price` as location_cost_price, `phppos_location_items`.`unit_price` as location_unit_price
FROM (`phppos_items`)
LEFT JOIN `phppos_categories` ON `phppos_categories`.`id` = `phppos_items`.`category_id`
LEFT JOIN `phppos_location_items` ON `phppos_location_items`.`item_id` = `phppos_items`.`item_id` and location_id = 1
LEFT JOIN `phppos_additional_item_numbers` ON `phppos_additional_item_numbers`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_items_tags` ON `phppos_items_tags`.`item_id` = `phppos_items`.`item_id`
LEFT JOIN `phppos_tags` ON `phppos_tags`.`id` = `phppos_items_tags`.`tag_id`
WHERE (MATCH (phppos_items.name, phppos_items.item_number, product_id, description) AGAINST ('test') 
or MATCH(phppos_tags.name) AGAINST ('test') or 
MATCH(phppos_categories.name) AGAINST ('test') or 
MATCH(phppos_additional_item_numbers.item_number) AGAINST ('test'))and phppos_items.deleted=0
LIMIT 20 

EXPLAIN:

| id | select_type | table                          | type   | possible_keys                        | key     | key_len | ref                            | rows  | Extra       |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
|  1 | SIMPLE      | phppos_items                   | ref    | deleted                              | deleted | 4       | const                          | 66679 | NULL        |
|  1 | SIMPLE      | phppos_categories              | eq_ref | PRIMARY                              | PRIMARY | 4       | pos.phppos_items.category_id   |     1 | NULL        |
|  1 | SIMPLE      | phppos_location_items          | eq_ref | PRIMARY,phppos_location_items_ibfk_2 | PRIMARY | 8       | const,pos.phppos_items.item_id |     1 | NULL        |
|  1 | SIMPLE      | phppos_additional_item_numbers | ref    | PRIMARY                              | PRIMARY | 4       | pos.phppos_items.item_id       |     1 | Using index |
|  1 | SIMPLE      | phppos_items_tags              | ref    | PRIMARY                              | PRIMARY | 4       | pos.phppos_items.item_id       |     1 | Using index |
|  1 | SIMPLE      | phppos_tags                    | eq_ref | PRIMARY                              | PRIMARY | 4       | pos.phppos_items_tags.tag_id   |     1 | Using where |
+----+-------------+--------------------------------+--------+--------------------------------------+---------+---------+--------------------------------+-------+-------------+
6 rows in set (0.00 sec)


mysql> show create table phppos_items;
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items | CREATE TABLE `phppos_items` (
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `item_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `tax_included` int(1) NOT NULL DEFAULT '0',
  `cost_price` decimal(23,10) NOT NULL,
  `unit_price` decimal(23,10) NOT NULL,
  `promo_price` decimal(23,10) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `reorder_level` decimal(23,10) DEFAULT NULL,
  `expire` date DEFAULT NULL,
  `item_id` int(10) NOT NULL AUTO_INCREMENT,
  `allow_alt_description` tinyint(1) NOT NULL,
  `is_serialized` tinyint(1) NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  `is_service` int(1) NOT NULL DEFAULT '0',
  `commission_percent` decimal(23,10) DEFAULT '0.0000000000',
  `commission_fixed` decimal(23,10) DEFAULT '0.0000000000',
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `item_number` (`item_number`),
  UNIQUE KEY `product_id` (`product_id`),
  KEY `phppos_items_ibfk_1` (`supplier_id`),
  KEY `name` (`name`),
  KEY `deleted` (`deleted`),
  KEY `phppos_items_ibfk_2` (`image_id`),
  KEY `phppos_items_ibfk_3` (`category_id`),
  FULLTEXT KEY `full_search` (`name`,`item_number`,`product_id`,`description`),
  FULLTEXT KEY `name_search` (`name`),
  FULLTEXT KEY `item_number_search` (`item_number`),
  FULLTEXT KEY `product_id_search` (`product_id`),
  FULLTEXT KEY `description_search` (`description`),
  FULLTEXT KEY `size_search` (`size`),
  CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`),
  CONSTRAINT `phppos_items_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`),
  CONSTRAINT `phppos_items_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table phppos_categories;
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `deleted` (`deleted`),
  KEY `parent_id_name_index` (`parent_id`,`name`),
  FULLTEXT KEY `name_search` (`name`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table phppos_location_items;
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                 | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_location_items | CREATE TABLE `phppos_location_items` (
  `location_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `location` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `cost_price` decimal(23,10) DEFAULT NULL,
  `unit_price` decimal(23,10) DEFAULT NULL,
  `promo_price` decimal(23,10) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `quantity` decimal(23,10) DEFAULT '0.0000000000',
  `reorder_level` decimal(23,10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`location_id`,`item_id`),
  KEY `phppos_location_items_ibfk_2` (`item_id`),
  CONSTRAINT `phppos_location_items_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `phppos_locations` (`location_id`),
  CONSTRAINT `phppos_location_items_ibfk_2` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table phppos_additional_item_numbers;
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_additional_item_numbers | CREATE TABLE `phppos_additional_item_numbers` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`item_id`,`item_number`),
  UNIQUE KEY `item_number` (`item_number`),
  FULLTEXT KEY `item_number_search` (`item_number`),
  CONSTRAINT `phppos_additional_item_numbers_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table phppos_items_tags;
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items_tags | CREATE TABLE `phppos_items_tags` (
  `item_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  PRIMARY KEY (`item_id`,`tag_id`),
  KEY `phppos_items_tags_ibfk_2` (`tag_id`),
  CONSTRAINT `phppos_items_tags_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `phppos_items` (`item_id`),
  CONSTRAINT `phppos_items_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `phppos_tags` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table phppos_tags;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                      |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_tags | CREATE TABLE `phppos_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_name` (`name`),
  KEY `deleted` (`deleted`),
  FULLTEXT KEY `name_search` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1

The optimizer rarely does anything useful OR constructs.

What you can attempt to do is

  1. Get ids from each of the MATCHs
  2. UNION the results
  3. JOIN back to the necessary tables to get the desired field.

Something like this:

SELECT i2.*, c2.name AS category, ...
    FROM (
       SELECT i.id
           FROM phppos_items AS i
           WHERE MATCH(...) AGAINST(...)
       UNION DISTINCT
       SELECT i.id
           FROM phppos_items AS i
           JOIN phppos_categories AS c  ON c.id = i.category_id
           WHERE MATCH(c.name) AGAINST(...)
       UNION DISTINCT
       ...
        ) AS u
    JOIN phppos_items AS i2  ON i2.id = u.id
    LEFT JOIN phppos_categories AS c2  ON c2.id = i.category_id
    ... ;

To further explain:

  • Each SELECT in the UNION should efficiently use the FULLTEXT index.
  • UNION DISTINCT will eliminate any dups.
  • AND deleted=0 could be added to each subquery or wait until the outer query.
  • Since the JOINs are 'running the opposite direction', you may need to add indexes such as i.category_id. (I did not check.)
  • Each inner SELECT will (hopefully) have very few (or zero) ids
  • The UNION will have a small number of ids.
  • The final set of JOINs will work on only on that small number, not all 66679; this is where the speedup will occur.
  • The total time is likely to be less, in spite of the complexity.
  • Thanks for the tip. I got rid of the OR's (Just searched less fields) and this got the search down to 200ms. – Chris Muench Aug 19 '15 at 15:46
0

I would recommend to reduce the amount of occurrences in the inner query over phppos_items. A way to do that is to get only the heavier records using a subquery. I see that you are using LIMIT 20, so I assume that you need online 20 records from the overall. Documentation

What you can do to do so, is to trim the working set using a condition over the match score. i.e.:

WHERE [...] (MATCH(phppos_tags.name) AGAINST ('test')) > 0.65

  • What do you mean by heavier records? – Chris Muench Aug 19 '15 at 2:22
  • @ChrisMuench MATCH ... AGAINST has something of a double role. In addition to being a search operator, if you evaluate it as an expression in a numeric context , it returns a relevance score for each row. If instead of relevance, we call that value "weight," then "heavier" must mean "more relevant." Hence ORDER BY (MATCH (...) AGAINST (...)). – Michael - sqlbot Aug 19 '15 at 10:02
  • @ChrisMuench as Michael pointed, I meant "relevance". Also, this query is over test data, which could not be representing a real case. I suggest you to create a sample working set closer as possible from your expected production environment. – 3manuek Aug 19 '15 at 22:39
0

MySQL FT search isn't that fast as we hope to be.

I'd recommend Sphinx - http://sphinxsearch.com/ - it provides fast and relevant full-text search.

If you are wondering how fast is Sphinx (from my experience), it can take few minutes to search 200M records with MySQL, but with Sphinx, less than 2 seconds, and better accuracy.

  • Wish I had the option. My software is installed on shared hosts and don't have access to this. – Chris Muench Aug 19 '15 at 14:52

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.