What is a suitable index for this query?

Question

1. Below query is taking long time - around 540 seconds.

2. The table did not have any indexes. I created indexes on columns in the WHERE clause and other fields but the query is not using these indexes.

3. Please suggest good indexes for this query.

Query:

select
    alias_to,
    fake_uri,
    concat(ifnull(parameters,''),ifnull(parameters2,''),ifnull(parameters3,'')) as parameters,
    precedence,
    template_name
from site_map_template
order by precedence, fake_uri desc;

Explain output:

+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | site_map_template | ALL  | NULL          | NULL | NULL    | NULL | 7616 | Using filesort |
+----+-------------+-------------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

site_map_template table:

CREATE TABLE `site_map_template` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `template_name` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `alias_to` varchar(100) COLLATE latin1_bin DEFAULT NULL,
  `package` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `fake_uri` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `precedence` int(11) DEFAULT NULL,
  `parameters` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `parameters2` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `parameters3` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  KEY `idx_1372` (`template_name`),
  KEY `idx_n1` (`precedence`),
  KEY `idx_n2` (`fake_uri`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin

Answer 1

Using SQL Server 2012 (hopefully there is an equivalent in MySQL):

CREATE TABLE dbo.site_map_template 
(
  row_mod datetime NULL,
  row_create datetime NULL,
  template_name varchar(50) COLLATE Latin1_General_BIN2 NULL,
  alias_to varchar(100) COLLATE Latin1_General_BIN2 NULL,
  package varchar(50) COLLATE Latin1_General_BIN2 NULL,
  fake_uri varchar(255) COLLATE Latin1_General_BIN2 NULL,
  precedence integer NULL,
  parameters1 varchar(255) COLLATE Latin1_General_BIN2 NULL,
  parameters2 varchar(255) COLLATE Latin1_General_BIN2 NULL,
  parameters3 varchar(255) COLLATE Latin1_General_BIN2 NULL
);

A good clustered index for this query is:

CREATE UNIQUE CLUSTERED INDEX PK__site_map_template_precedence_fake_uri
ON dbo.site_map_template (precedence, fake_uri DESC);

Or, a nonclustered index:

CREATE NONCLUSTERED INDEX nc1
ON dbo.site_map_template
    (
    precedence,
    fake_uri DESC
    )
INCLUDE
    (
    alias_to,
    parameters1,
    parameters2,
    parameters3,
    template_name
    );

The query (again, in SQL Server syntax) is:

SELECT
    alias_to, 
    fake_uri,
    [parameters] = CONCAT(parameters1, parameters2, parameters3),
    precedence, 
    template_name 
FROM dbo.site_map_template
ORDER BY
    precedence, 
    fake_uri DESC;

And the execution plan is a simple scan of the index:

Or, using the nonclustered index:

The important thing in both cases is that each index covers all the columns needed by the query, and the index keys are precedence ASC and fake_uri DESC. Without the descending second key, a full sort would be required, and this is likely the cause of your slow performance.