I am attempting to optimize this question:

Choose articles.id

FROM articles

INNER JOIN articles_authors ON articles.id=articles_authors.fk_Articles

WHERE articles_authors.fk_Authors=586

ORDER BY articles.publicationDate LIMIT ,50

Table articles :

  • Engine : MyISAM
  • Row_format: Dynamic
  • Rows : 1 482 588
  • Data_length : 788 926 672
  • Max data length : 281 474 976 710 655
  • Index length : 127 300 608
  • data free :
  • checksum : null
    CREATE TABLE `articles` (

      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    `title` VARCHAR(255) NOT NULL,

    `publicationDate` DATE NOT NULL DEFAULT '1970-01-01',

    PRIMARY KEY (`id`),

    KEY `publicationDate` (`publicationDate`)

    ) ENGINE=MYISAM AUTO_INCREMENT=1498496 DEFAULT CHARSET=utf8 

Table articles_authors :

  • Engine : MyISAM
  • Row_format: Dynamic
  • Rows : 1 970 750
  • Data_length : 45 008 420
  • Max data length : 281 474 976 710 655
  • Index length : 127 300 608
  • data free :
  • checksum : null
    CREATE TABLE `articles_authors` (

    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

    `fk_Articles` int(10) unsigned NOT NULL,

    `fk_Authors` int(10) unsigned NOT NULL,

    PRIMARY KEY (`id`),

    UNIQUE KEY `fk_Articles_fk_Authors` (`fk_Articles`,`fk_Authors`),

    KEY `fk_Articles` (`fk_Articles`),

    KEY `fk_Authors` (`fk_Authors`),

    ) ENGINE=MyISAM AUTO_INCREMENT=2349047 DEFAULT CHARSET=utf8 

Explain on query :

id (1), choose_type(SIMPLE), TABLE(articles_authors), TYPE(ref), possible_secrets(fk_Articles_fk_Authors, fk_Articles, fk_Authors), KEY (fk_Authors), Key_len(4), ref(const), ROWS(171568), extra (USING TEMPORARY USING FILE sort)

id (1), choose_type(SIMPLE), TABLE(articles), TYPE(eq_ref), possible_secrets(PRIMARY), KEY (PRIMARY), Key_len(4), ref(articles_authors.fk_Authors), ROWS(1), extra ()

As you can tell, the SQL totally not enhanced (using file sort in explain).

Interesting help!

It is while using index, enjoy it states within the explain.

id (1), choose_type(SIMPLE), TABLE(articles_authors), TYPE(ref),
possible_secrets(fk_Articles_fk_Authors, fk_Articles, fk_Authors),

KEY (fk_Authors), Key_len(4), ref(const), ROWS(171568),
extra (USING TEMPORARY USING FILE sort)

Only being an extra for that 50 rows it chooses and than orders by publication date will it perform a filesort.
It produces a brief table with 50 products. So it then sorts with tablesort.
This has to become done by doing this, because MySQL cannot make use of the large index on individuals lonely 50 products, it might cost to much in IO-access time.

It's faster to to some sort on 50 amounts in memory then to gain access to the index on disk.

That you can do something to accelerate the query though:

optimize table articles, articles_authors

and rerun the query.

EDIT: Accelerate suggestion by denormalizing table articles

Should you rewrite the query such as this:

Choose articles.id FROM articles WHERE articles.id IN (

  Choose articles_authors.fk_articles WHERE articles_authors.fk_authors = 586

  LIMIT ,50

)

ORDER BY articles.publicationDate

You'll most likely begin to see the same performance, however it highlights the issue. If author 586 has 180,000 articles, then MySQL needs to search 50 products from 180k in articles_authors after which search 50 products from 180k again within the order table.

Should you merge the tables article_authors and articles, your table articles is going to be denormalized (presuming articles might have multiple authors) but it's not necessary to perform the join and also you save the second search.

CREATE TABLE `articles` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `publicationDate` date NOT NULL DEFAULT '1970-01-01',

  `title` varchar(255) NOT NULL,

  `fk_Authors` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `Articles_fk_Authors` (`id`,`fk_Authors`),

KEY `fk_Authors` (`fk_Authors`),

KEY `publicationDate` (`publicationDate`)

) ENGINE=MyISAM AUTO_INCREMENT=2349047 DEFAULT CHARSET=utf8

You can now choose from this like so

Choose articles.id FROM articles WHERE articles.Author = 586

ORDER BY articles.publicationDate LIMIT 50,