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.

I've a table with a multi-column UNIQUE index on _job_id__ and __keyword_id__.

Would I also need to add another index to __job_id__ if I have a frequent query which performs a GROUP BY on that column?

(at 100 million rows it could take a while.This is why I'm asking instead of just doing)

share|improve this question
    
This a good question (+1) for the community as a reminder to keep indexes as lean and mean as possible for any table. –  RolandoMySQLDBA May 18 '11 at 16:06
add comment

3 Answers 3

up vote 3 down vote accepted

No, not at all !!! The MySQL Query Optimizer will do the right thing if the main column(s) needed is leftmost in the index. If you did make such an index, the MySQL Query Optimizer may opt never to use that index if you always perform GROUP BY job_id,keyword_id. MySQL Query Optimizer may or may not use the index if you gather records by job_id only, but then you have a redundant index wasting space anyway.

If the table is MyISAM, making such an index would just bloat the MYI file.

If the table is InnoDB and innodb_file_per_table is 0, making such an index would just bloat ibdata1.

If the table is InnoDB and innodb_file_per_table is 1, making such an index would just bloat the .ibd file of the table.

In summary, you do not need to make that additional index !!!

share|improve this answer
1  
dang now I need another idea why my query is so slow :) –  JIStone May 17 '11 at 23:33
1  
+1 for the answer, perhaps you could explain the leftmost index rule a bit further for him (or is that worthy of a separate question!) –  Derek Downey May 17 '11 at 23:34
    
@DTest That is worthy of a separate question !!! –  RolandoMySQLDBA May 18 '11 at 2:34
add comment

Indexes can only speed up group by operations by reducing sorting - this will be more efficient if the index used is the clustered index or at least has the same leading column as the clustered index. In all this I am assuming MySQL has no equivalent of a hash group by operation which would usually bypass any benefit of indexes at all - maybe someone else can confirm this.

There is a marginal benefit to having a separate index on job_id assuming that is the only column in the group by clause and neither is the clustered index: the index will be smaller and therefore scanning it will generate less I/O

--EDIT--

As an index contains all the primary key fields defined for the clustered index key that are not in the secondary index, An index on job_id will only be smaller than an index on job_id, keyword_id if keyword_id is not part of the clustered index.

share|improve this answer
    
If job_id had a separate index to itself, it would still be bloaty because every job_id key entry would have a rowid from the gen_clust_index (serverfault.com/questions/238845/…) attached as well. Worse off, the MySQL Query Optimizer may play Russian Roulette deciding between job_id nonclustered index and the clustered job_id-keyword_id index as the index of choice for any EXPLAIN plan it devises. However, you still make an excellent point on the reducing sorting in favor of the clustered indexed. That's +1 in my book. –  RolandoMySQLDBA May 18 '11 at 16:03
    
@Rolando - "assuming ... neither is the clustered index". –  Jack Douglas May 22 '11 at 14:21
    
@Rolando - but I take your point about bloat - I'll edit my post, thanks. –  Jack Douglas May 22 '11 at 14:33
    
Sorry I missed that. You did mentioned that before. –  RolandoMySQLDBA May 22 '11 at 16:52
add comment

If your real question is about a slow query, then please provide

SHOW CREATE TABLE

SHOW TABLE STATUS

SHOW VARIABLES LIKE '%buffer%'

EXPLAIN SELECT ...

How much RAM is available?

There are many possible reasons; most can be spotted by looking over those items.

share|improve this answer
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.