Adding fields to optimize MySQL queries

Question

I have a MySQL table with 3 fields:

• Location
• Variable
• Value

I frequently use the following query:

SELECT * 
  FROM Table 
 WHERE Location = '$Location' 
   AND Variable = '$Variable' 
ORDER BY Location, Variable

I have over a million rows in my table and queries are somewhat slow. Would it increase query speed if I added a field VariableLocation, which is the Variable and the Location combined? I would be able to change the query to:

SELECT * 
  FROM Table 
 WHERE VariableLocation = '$Location$Variable' 
ORDER BY VariableLocation

Answer 1

You are right, this time around, mysql will be dealing with one field rather than two which will definitely increase performance.

You may also consider adding index to your fields.

Answer 2

Try adding an index which covers the two fields you should then still get a performance boost but also keep your data understandable because it wouldn't seem like the two columns should be combine but you are just doing it to get performance.

Answer 3

I would add a covering index, for columns location and variable:

ALTER TABLE 
  ADD INDEX (variable, location);

...though if the variable & location pairs are unique, they should be the primary key.

Combining the columns will likely cause more grief than it's worth. For example, if you need to pull out records by location or variable only, you'd have to substring the values in a subquery.

Answer 4

I would advise against combining the fields. Instead, create an index that covers both fields in the same order as your ORDER BY clause:

ALTER TABLE tablename ADD INDEX (location, variable);

Combined indices and keys are only used in queries that involve all fields of the index or a subset of these fields read from left to right. Or in other words: If you use location in a WHERE condition, this index would be used, but ordering by variable would not use the index.

When trying to optimize queries, the EXPLAIN command is quite helpful: EXPLAIN in mysql docs