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.

As far as I know, an InnoDB index always contains the primary index.

I cannot get the following to work:

SELECT t1.a FROM table_1 t1 LEFT JOIN table_2 t2 on t1.id = t2.refid 
WHERE t1.b = 99
GROUP BY t1.id
ORDER BY t1.c

I have these keys:

t1.id = PRIMARY KEY
(b,c) = key

When I EXPLAIN this I get "Using where; Using temporary; Using filesort".

I do use my index on (b,c) but it doesn't use the primary index in that index to get rid of the temp table and filesort.

When I change the GROUP BY to GROUP BY c, I get rid of the temp table and filesort.

Added the primary key field to key (b,c) did not help either.

What am i doing wrong?


Update

Actual query:

SELECT q.id AS q__id, q.user_id AS q__user_id, q.title AS q__title, q. NAME AS q__name
     , COUNT(r.id) AS r__0   
FROM questionnaire q 
  LEFT JOIN response r 
    ON q.id = r.questionnaire_id 
WHERE ( q.user_id = 2 ) 
GROUP BY q.id 
ORDER BY q.updated_at DESC ;

Tables:

CREATE TABLE `questionnaire` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `title` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(18) COLLATE utf8_unicode_ci NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `thankyou` text COLLATE utf8_unicode_ci,
  `status` enum('inactive','active') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'inactive',
  `published_at` datetime DEFAULT NULL,
  `license_id` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique_name_idx` (`user_id`,`name`) USING BTREE,
  KEY `questionnaire_license_id_license_id` (`license_id`),
  KEY `deleted` (`deleted_at`),
  KEY `updated_deleted` (`user_id`,`updated_at`) USING BTREE,
  CONSTRAINT `questionnaire_license_id_license_id` 
    FOREIGN KEY (`license_id`) 
    REFERENCES `license` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `response` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `questionnaire_id` int(10) unsigned NOT NULL,
  `ip` varchar(39) DEFAULT NULL,
  `headers` text,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `response_questionnaire_id_questionnaire_id` (`questionnaire_id`),
  CONSTRAINT `response_questionnaire_id_questionnaire_id` 
    FOREIGN KEY (`questionnaire_id`) 
    REFERENCES `questionnaire` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

Explain for GROUP BY IdWith Group BY Id Explain for GROUP BY update_atWith Group By update_at

share|improve this question
    
First of all, "t1", in your example, is a table, not a Primary Key. Also, you miss the "ON" clause in the JOIN, which makes me wonders how can this query actually work. Additionally, I'd suggest to format the SQL to make it more readable. Simply highlight it and select "Code" from the editor, it will format it automatically. –  Diego Aug 9 '12 at 22:28
    
Updated it, but can you help me? –  Michiel Thalen Aug 10 '12 at 9:39
    
Would you be able to add the CREATE statements of the two tables involved, including all indexes on each? –  Diego Aug 10 '12 at 13:00
add comment

migrated from stackoverflow.com Aug 10 '12 at 23:22

This question came from our site for professional and enthusiast programmers.

1 Answer

Because you only select columns from table_1 and because table_2 is only joined (on the right side) of the LEFT JOIN and none of its columns is used elsewhere (SELECT, WHERE, GROUP BY or ORDER BY clauses), you can completely remove the LEFT JOIN table_2 t2 on t1.id = t2.refid part.

This might have given less rows in the result but because you group by the Primary key of table_1 after the join, there is no such case.

SELECT t1.a 
FROM table_1 t1 
WHERE t1.b = 99
GROUP BY t1.id
ORDER BY t1.c ;

Now, because you group by the Primary key of table_1, which is redundant when you have only one table, you can also remove that part: GROUP BY t1.id

Finally, the query is equivalent to:

SELECT t1.a 
FROM table_1 t1 
WHERE t1.b = 99
ORDER BY t1.c ;

which should use the index on (b, c), which yes, includes the primary key. You may consider it to be (b, c, id) if you want.

But whether that index will be used is not 100% sure. Depending on the selectivity of column b (what percent of the whole table has b=99?) this index may or may not be used. If there a lot of rows with b=99 (a large percentage), the optimizer may choose to scan the whole table instead and do a filesort, than use the index, select those (99,c) combinations that exist and then hit the table to find the a values.

If you have an index on (b, c, a) or on (b, c, id, a), the query will be able to find all the info that is needed in this index and in the correct order, so it will use it.

share|improve this answer
    
Ok I guess it was not clear that this is the actual query. I stripped out some parts which are not relevant. I DO use the JOIN and GROUP BY so i cannot leave it out. Selectivity has nothing to do with this. I use the "EXPLAIN" command to see which keys can be used, and what is going on. I could also just force a key, but still i can see the temp table and filesort. I will update with the real tables and query. –  Michiel Thalen Aug 12 '12 at 12:29
    
SELECT q.id AS q__id, q.user_id AS q__user_id, q.title AS q__title, q. NAME AS q__name, COUNT(r.id) AS r__0 FROM questionnaire q LEFT JOIN response r ON q.id = r.questionnaire_id WHERE ( q.user_id = 2 ) GROUP BY q.id ORDER BY q.updated_at DESC –  Michiel Thalen Aug 12 '12 at 12:33
    
Please add the SHOW CREATE TABLE output for the 2 tables and the EXPLAIN plan –  ypercube Aug 12 '12 at 12:33
    
We need the response table, not the licence one. And you can add the info in the question (click on Edit), no need for external links. –  ypercube Aug 12 '12 at 12:44
    
I meant edit the question not this answer :) Moved the info there. –  ypercube Aug 12 '12 at 13:02
show 1 more 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.