Optimising a SELECT, when filtering one to many fields, using `LIKE "%value%"`

Question

I've got a table of customer orders, on a MariaDB (MySQL) server, using InnoDB, 2GB in size, with 3 million records.

Most SELECT's use an index, and respond reasonably fast, but the admin have a rarely used "advanced search" feature, which is taking a long time to process.

It takes ~10 seconds to find the first 50 results; and if that query returns 50 results (or loading page 2+ of the results), it takes a further ~30 seconds to COUNT(id) the results (used to show the total number of results, and provide pagination links).

It used to use SQL_CALC_FOUND_ROWS, but that was removed last year, as its been deprecated since MySQL 8.0.17 (2019-07-22); and will presumably be removed from MariaDB in the future.

The admin can search based on one or more fields, e.g.

• First Name
• Last Name
• Postcode
• Mobile Number
• Telephone Number
• Email Address

If they provide a value for a field, it's added to the SELECT query, and it uses field LIKE "%value%" so it can do a partial match on values.

The results are also sorted by a created date, and excluded if the order has been cancelled (which uses a DATETIME field).

I don't think normal indexing is going to work, so was wondering what other options might help?

There is a FULLTEXT index on a field that contains a lot of keywords from the order (including values from the listed search fields), this works well for the normal search feature, but I don't think I'll be able to use it here.

Two weeks ago the database used to run locally, when it was using MyISAM, on a "t2.medium" EC2 server (4GB RAM, but also running Apache/PHP/etc), and this search feature worked fairly well (~2 seconds)... but it's now on a "db.t4g.small" AWS RDS server (2GB of RAM), and uses InnoDB, it's considerably slower.

It's possible to upgrade to a more powerful server, but everything else seems to be fine... and the database is 14 GB in total, presuming that it would need to all be loaded into RAM (the advanced search is rarely used), the 16GB server is about x7 the price ($25.55 vs $173.14).

---

While I believe this is a general question (how to search several text fields in a big table); for some more specific details, the SQL would be something like:

SELECT
    o.id,
    o.created,
    o.payment_received,
    o.settled,
    o.dispatched,
    o.first_name,
    o.last_name,
    o.email
FROM
    orders AS o
WHERE
    o.first_name LIKE "%Craig%" AND
    o.last_name LIKE "%Francis%" AND
    o.address_post_code LIKE "%10%" AND
    o.cancelled = "0000-00-00 00:00:00"
ORDER BY
    payment_received DESC
LIMIT
    0, 50;

The fields searched will depend on what the admin has provided.

And if 50 results are returned, then it will also run:

SELECT
    COUNT(o.id) AS c
FROM
    orders AS o
WHERE
    o.first_name LIKE "%Craig%" AND
    o.last_name LIKE "%Francis%" AND
    o.address_post_code LIKE "%10%" AND
    o.cancelled = "0000-00-00 00:00:00";

The table structure uses tinytext fields for these searchable fields.

It does have 77 columns, but most are unrelated for this (e.g. vat, discounts, tracking information), so I'll abbreviate it to the following:

CREATE TABLE orders (
  id int(11) NOT NULL AUTO_INCREMENT,
  created datetime NOT NULL,
  payment_received datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  settled datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  dispatched datetime NOT NULL,
  email varchar(100) NOT NULL,
  first_name tinytext NOT NULL,
  last_name tinytext NOT NULL,
  address_line_1 tinytext NOT NULL,
  address_line_2 tinytext NOT NULL,
  address_line_3 tinytext NOT NULL,
  address_city tinytext NOT NULL,
  address_post_code tinytext NOT NULL,
  address_country tinytext NOT NULL,
  address_telephone tinytext NOT NULL,
  address_mobile tinytext NOT NULL,
  cancelled datetime NOT NULL,
  cache_keyword text NOT NULL,
  PRIMARY KEY (id),
  KEY email (email),
  KEY settled (settled),
  KEY payment_received (payment_received,cancelled),
  FULLTEXT KEY cache_keyword (cache_keyword)
);

The cache_keyword is an interesting one, as it gets populated with a CONCAT() of the searchable field values, this works great for the normal search, but does not work in this "advanced search", as the admin are typically searching for partial values.

The ANALYZE for this query is predictably boring, but I do occasionally see it using the payment_received index (presumably for the ORDER BY).

id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
1	SIMPLE	o	ALL	NULL	NULL	NULL	NULL	3163161	30.00	100.00	100.00	Using where; Using filesort

Answer 1

• Use InnoDB, not MyISAM.
• If there has been a lot of churn (deletes, inserts, updates) on the table, MyISAM tends to get fragmented, hence slow.
• LIKE '%...' cannot use an index. FULLTEXT, if practical is much faster.
• "The admin can search based on one or more fields". Have multiple indexes -- preferably composite. Capture the slowlog and craft optimal indexes from the slow queries. Index Cookbook
• FOUND_ROWS is going away. Suggest switching to the 'replacement' of having 2 queries. It is messier and slower.
• OFFSET for pagination gets slower and slower. Avoid it if you can: Pagination

Provide some specific queries; we can provide specific tips.