CODEWITHSUNDEEP
mysqlquery-performance
www.diazis.com
www.diazis.com

Reputation: 96

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?

Upvotes: 0

Views: 57

Answers (2)

Rick James
Rick James

Reputation: 142208

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)

Upvotes: 1

www.diazis.com
www.diazis.com

Reputation: 96

Certainly a mistake when creating the index the first time. Will test again and see if this issue appear again. Thanks for the comments.

Upvotes: 0

Related Questions