Adding fields to optimize MySQL queries

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 = '$开发者_如何学PythonLocation$Variable' 
ORDER BY VariableLocation

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.

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.

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

Correction Update:

Courtesy: @paxdiablo:

A column in the table will make no difference. All you need is an index over both columns and the MySQL engine will use that. Adding a column in the table is actually worse than that since it breaks 3NF and wastes space. See http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html which states: SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly.