Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
  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
share|improve this question
1  
There is no WHERE clause in your query, and the ORDER BY specifies two columns which is not in your single index (idx_1372). –  dezso Feb 18 '13 at 9:38
    
I was tried with single indexes and composite indexes on order by column but no luck. –  ashuthosh Feb 18 '13 at 9:42
1  
Your table definition shows no sign of those indexes, I bet they don't exist. –  dezso Feb 18 '13 at 9:45
1  
What happens if you add a composite index of (precedence, fake_uri)? –  dezso Feb 18 '13 at 9:51
1  
let us continue this discussion in chat –  ashuthosh Feb 18 '13 at 9:55
show 2 more comments

1 Answer

up vote 4 down vote accepted

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:

Clustered index plan

Or, using the nonclustered index:

Nonclustered index plan

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.

share|improve this answer
3  
MySQL (simple) indexes can be used for either ASC or DESC ordering. Unfortunately compound indexes will be useful only for ASC, ASC or DESC, DESC orderings. Mixed types will probably not use the index. –  ypercube Feb 18 '13 at 10:40
3  
Thanks ypercube. I wonder if @ashuthosh could change the query so the ordering is the same for both index columns? I think I'll leave it to the MySQL experts to decide the best solution from here :) –  Paul White Feb 18 '13 at 10:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.