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)
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 !!!
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.
