0

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?

Improve this question
6
  • Explain statement results would be helpful Commented Oct 19, 2018 at 10:07
  • It would be nice if you could post the output of SHOW CREATE TABLE table1. It would be critical to post the output of EXPLAIN SELECT * FROM ..... Commented Oct 19, 2018 at 10:07
  • You have not provided enough information for us to help you. Please read this article about asking good questions about SQL. Please pay special attention to the section on query performance. Then please edit your question. Commented Oct 19, 2018 at 10:18
  • is orders numeric or string? in your second SQL you manage it as a numeric field... Commented Oct 19, 2018 at 10:21
  • Agree that I haven't provided enough information. I tried to make the question clearer by presenting it in a simple way. But I understand It was a bad idea... Commented Oct 19, 2018 at 10:22

2 Answers 2

Reset to default
1

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

However, why if we query an integer on a varchar field mysql don't use the index? It seems to be strange for me as I would expect that mysql cast automaticaly the input to the expected type. I don't see the interest to not do so.
@www.diazis.com - In it's infinite wisdom, MySQL (or may be the ANSI standard) decided that the literal dominates, and must convert the column to numeric. But, the column is assuming that it will be compared against strings, hence it is ill-prepared for comparing against numbers.
To further confuse things, if you have '344A7884' in the orders column, you will find that orders = 344 succeeds! This is because of the way it converts strings to numbers. And there are anomolies like '2' > '10' because of the way strings compare.
0

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

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.