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:

    orders AS o
    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"
    payment_received DESC
    0, 50;

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

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

    COUNT(o.id) AS c
    orders AS o
    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:

  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,
  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
  • 1
    For a concrete answer, you would need to add details such as an example slow query, the EXPLAIN ANALYZE for that query, your tables definitions that are used in that query, and all index definitions on those tables. Sample data would be helpful too.
    – J.D.
    Commented Jun 6, 2023 at 12:35
  • Good point @J.D., while I was thinking it was a fairly generic question, having an example table structure, and example SQL used to query it would help (but I can't show the 3 million records, so hopefully you can use your imagination for that bit). Commented Jun 7, 2023 at 1:19
  • 'but I can't show the 3 million records, so hopefully you can use your imagination for that bit" - Heh, no problem, we can be a little imaginative. Just a few rows of data is perfect. More importantly is the table / index definitions, and the EXPLAIN ANALYZE of the slow query, anyway.
    – J.D.
    Commented Jun 7, 2023 at 1:47

1 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.

  • Thanks @rick-james... just to note that I am using InnoDB (I've not tested properly, but I think that made things worse); it's a table of many INSERT's (not many UPDATE's, and not had any DELETE's yet); the search needs to use LIKE '%...%' and is why I don't think FULLTEXT can help here; which fields are searched is dependent on user (so many indexes would be difficult, if they were even used); I have already removed FOUND_ROWS; and pagination isn't important yet (this is happening on page 1, but they still need a total count of results). Commented Jun 7, 2023 at 1:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.