mysql - Can I force 2 separate index on GROUP BY and ORDER BY? -

i'm trying group column, , right after order different column. they're both indices, aren't used unless force them.

here's query:

select a.*  (      select      id           articles temp force index (id_pub_date)      inner join      sources_articles sa force index (source_id) on temp.id = sa.article_id           sa.source_id in (10,11,12,13,15,19,33,37,40,41,46)      group id      order pub_date desc      limit 0,10  ) id_array  inner join articles on a.id = id_array.id 

here's database schema: articles has 160,000 rows , sources_articles has 200,000 rows.

drop table if exists `articles`; create table `articles` (   `id` mediumint(8) unsigned not null auto_increment,   `title` char(255) collate utf8_unicode_ci not null,   `img_url` char(255) character set latin1 not null,   `vid_url` varchar(511) collate utf8_unicode_ci not null,   `vid_content_type` varchar(31) collate utf8_unicode_ci not null,   `vid_thumb_url` varchar(255) collate utf8_unicode_ci not null,   `description` text collate utf8_unicode_ci not null,   `url` varchar(511) character set latin1 not null,   `date` int(10) unsigned not null,   `pub_date` int(10) unsigned not null,   `comment_url` char(255) character set latin1 not null,   primary key (`id`),   unique key `url` (`url`),   key `id_pub_date` (`id`,`pub_date`),   key `pub_date` (`pub_date`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci;   drop table if exists `sources`; create table `sources` (   `s_id` tinyint(3) unsigned not null auto_increment,   `s_name` char(255) character set latin1 not null,   `s_short_name` varchar(255) collate utf8_unicode_ci not null,   `s_slug` varchar(255) collate utf8_unicode_ci not null,   `s_category` varchar(255) collate utf8_unicode_ci not null,   `s_default` tinyint(1) not null,   `s_active` tinyint(1) not null,   primary key (`s_id`),   key `s_slug` (`s_slug`),   key `s_default` (`s_default`,`s_active`) ) engine=innodb default charset=utf8 collate=utf8_unicode_ci comment='list of sources';   drop table if exists `sources_articles`; create table `sources_articles` (   `sa_id` mediumint(8) unsigned not null auto_increment,   `source_id` tinyint(3) unsigned not null,   `article_id` mediumint(8) unsigned not null,   primary key (`sa_id`),   key `source_id` (`source_id`),   key `article_id` (`article_id`),   constraint `sources_articles_ibfk_1` foreign key (`source_id`) references `sources` (`s_id`) on delete no action on update no action,   constraint `sources_articles_ibfk_2` foreign key (`article_id`) references `articles` (`id`) on delete no action on update no action ) engine=innodb default charset=utf8 collate=utf8_unicode_ci; 

the query takes 600ms run. i'm using index made out of id , pub_date columns.

if force id index on temp, group by , order by id, query takes 3ms.

same if force pub_date index , group by , order by pub_date.

i thinking if force index id when grouping, , force pub_date index when ordering run lot faster. understand isn't possible on mysql.

of course might looking @ whole thing wrong.

i'd appreciate tips or :)

edit:

was able shave off couple hundred ms new, updated query:

select distinct a.*  (      select     id          articles temp force index (id_pub_date)      inner join     sources_articles sa force index (source_id_article_id) on temp.id = sa.article_id          sa.source_id in (10,11,12,13,15,19,33,37,40,41,46)       order pub_date desc      limit 0,10  ) id_array  inner join  articles on a.id = id_array.id 

with indexes describe, can't replicate finding:

drop table if exists `articles`; create table `articles`  (article_id int not null auto_increment primary key ,pub_date date not null  ,index(pub_date) );  drop table if exists `sources`; create table `sources`  (source_id int not null auto_increment primary key);  drop table if exists `sources_articles`; create table `sources_articles`  (source_id int not null ,article_id int not null ,primary key (source_id,article_id) );  select count(*) articles; +----------+ | count(*) | +----------+ |   131072 | +----------+  select count(*) sources; +----------+ | count(*) | +----------+ |     1024 | +----------+  select count(distinct source_id),count(distinct article_id),count(*) sources_articles; +---------------------------+----------------------------+----------+ | count(distinct source_id) | count(distinct article_id) | count(*) | +---------------------------+----------------------------+----------+ |                      1001 |                     112649 |   261748 | +---------------------------+----------------------------+----------+   select sql_no_cache distinct a.*                         articles                         join sources_articles sa                            on sa.article_id = a.article_id                        sa.source_id in (10,11,12,13,15,19,33,37,40,41,46)                         order                            a.pub_date desc, a.article_id                         limit 0,10; +------------+------------+ | article_id | pub_date   | +------------+------------+ |      72230 | 2015-06-23 | |      90398 | 2015-06-21 | |      72378 | 2015-06-21 | |      87814 | 2015-06-20 | |      66270 | 2015-06-19 | |       8399 | 2015-06-19 | |      21798 | 2015-06-18 | |      95773 | 2015-06-16 | |      67165 | 2015-06-15 | |      19615 | 2015-06-14 | +------------+------------+ 10 rows in set (0.05 sec)  +----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+ | id | select_type | table | type   | possible_keys | key     | key_len | ref                 | rows |                                                     | +----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+ |  1 | simple      | sa    | range  | primary       | primary | 4       | null                | 2741 | using where; using index; using temporary; using filesort | |  1 | simple      |     | eq_ref | primary       | primary | 4       | world.sa.article_id |    1 |                                                           | +----+-------------+-------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+ 

your query fast too, mine has prettier explain:

select a.*        ( select temp.article_id          articles temp          join sources_articles sa             on temp.article_id = sa.article_id         sa.source_id in (10,11,12,13,15,19,33,37,40,41,46)         group             article_id         order             pub_date desc         limit 0,10      ) id_array   join articles      on a.article_id = id_array.article_id; +------------+------------+ | article_id | pub_date   | +------------+------------+ |      72230 | 2015-06-23 | |      90398 | 2015-06-21 | |      72378 | 2015-06-21 | |      87814 | 2015-06-20 | |      66270 | 2015-06-19 | |       8399 | 2015-06-19 | |      21798 | 2015-06-18 | |      95773 | 2015-06-16 | |      67165 | 2015-06-15 | |      19615 | 2015-06-14 | +------------+------------+ 10 rows in set (0.04 sec)  explain select a.*        ( select temp.article_id          articles temp          join sources_articles sa             on temp.article_id = sa.article_id         sa.source_id in (10,11,12,13,15,19,33,37,40,41,46)         group             article_id         order             pub_date desc         limit 0,10      ) id_array   join articles      on a.article_id = id_array.article_id; +----+-------------+------------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+ | id | select_type | table      | type   | possible_keys | key     | key_len | ref                 | rows |                                                     | +----+-------------+------------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+ |  1 | primary     | <derived2> |    | null          | null    | null    | null                |   10 |                                                           | |  1 | primary     |          | eq_ref | primary       | primary | 4       | id_array.article_id |    1 |                                                           | |  2 | derived     | sa         | range  | primary       | primary | 4       | null                | 2741 | using where; using index; using temporary; using filesort | |  2 | derived     | temp       | eq_ref | primary       | primary | 4       | world.sa.article_id |    1 |                                                           | +----+-------------+------------+--------+---------------+---------+---------+---------------------+------+-----------------------------------------------------------+