Search code examples
mysqlperformanceindexingquery-optimization

Huge performance drop for LIKE statement if column order changes in composite index

Edit:
EXPLAIN result for the first two queries when using src_cus_lang: enter image description here EXPLAIN result for the last two queries when using cust_lang_src: enter image description here

Original post:
I'm using MySQL and have a table as follows:

CREATE TABLE tm(
    id INT AUTO_INCREMENT PRIMARY KEY,
    source TEXT(7000) NOT NULL,
    target TEXT(6000) NOT NULL,
    language CHAR(3),
    customer VARCHAR(10))

When using composite index (source(755), customer, language), the fetch times are as follows:

SELECT source FROM tm WHERE customer = 'Customer A' AND language = 'ENU' AND source LIKE '%.net%';  -- 0.015s
SELECT source FROM tm WHERE source LIKE '%.net%' AND customer = 'Customer A' AND language = 'ENU';  -- 0.015s

However, when using composite index (customer, language, source(755)), the fetch times increased drastically:

SELECT source FROM tm WHERE customer = 'Customer A' AND language = 'ENU' AND source LIKE '%.net%';  -- 1.7s
SELECT source FROM tm WHERE source LIKE '%.net%' AND customer = 'Customer A' AND language = 'ENU';  -- 1.7s

This is contrary to what I found somewhere on the Internet, which claims that the leftmost prefix rule is applied until <, >, BETWEEN or LIKE is met. According to this claim, the (customer, language, source(755)) index would be faster than (source(755), customer, language) index regarding fetch time, since source is used with LIKE. I can't post the source, because it's not in English. Is this claim wrong? If not, why did I see contrary results?

Solution

  • (In addition to what Bill says...)

    This may be the best:

    PRIMARY KEY(customer, language, id),  -- to optimize the query (and maybe others)
INDEX(id)   -- to keep AUTO_INCREMENT happy

    Probably your first two tries were "fast" because they ignored the index. Use EXPLAIN SELECT ... to verify.