Search code examples
mysqlquery-performance

Issue for optimizing mysql index

I have a mysql table with approximatively 1.5 million lines. This table contains a column called companies (integer) which contains few different values (3 or 4) and another column called orders (varchar) which contain a lot of different values (but some identical).

I created an multi-column index (type INDEX) with columns companies and orders.

I have a simple query 

SELECT * FROM TABLE1 WHERE companies = 1 AND orders = '344A7884'

There is no execution time difference (around 4 secondes) when I execute this query with the index implemented or without.

The only way I found to get an execution time of around 1 seconde is to create an index only on "orders" and run the following transformed query:

SELECT * FROM (SELECT * FROM TABLE1 WHERE orders = 34467884) RQ1 WHERE companies = 1

That seems not to be very proper. Can someone explain me this strange behavior and suggest a better way to optimize the index?

Solution

  • If an indexed column is a VARCHAR then do not test it against a number without quotes. For example, if companies is VARCHAR(99),

    companies = 1  -- will scan the entire table (slow)
companies = '1'  -- can use the index (fast)

    For further discussion, please provide SHOW CREATE TABLE and EXPLAIN SELECT ...

    If companies is, say, INT and orders is a VARCHAR, then

    WHERE companies = 1 AND orders = '344A7884'

    would work very fast with either of these:

    INDEX(companies, orders)
INDEX(orders, companies)