I'm trying to optimize this query:
SELECT 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 0,50;
Table articles :
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 :
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), select_type(SIMPLE), TABLE(articles_authors), TYPE(ref), possible_keys(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), select_type(SIMPLE), TABLE(articles), TYPE(eq_ref), possible_keys(PRIMARY), KEY (PRIMARY), Key_len(4), ref(articles_authors.fk_Authors), ROWS(1), extra ()
As you can see, the SQL query is not optimized (using file sort in explain).
Thanks for your help!
If you combine LIMIT row_count with ORDER BY , MySQL stops sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast.
The order of the conditions in the ON clause doesn't matter. The queries per query pair you are showing are equal. If one query in a pair gives you different rows than the other, then this cannot be caused by the queries you are showing.
The MySQL ORDER BY Keyword The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.
“Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there's no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan.
Maybe this will help you:
SELECT articles.id
FROM articles
INNER JOIN (SELECT fk_Articles FROM articles_authors WHERE articles_authors.fk_Authors=586) sub ON articles.id=sub.fk_Articles
ORDER BY articles.publicationDate LIMIT 0,50;
It is using the index, like it says in the explain.
id (1), select_type(SIMPLE), TABLE(articles_authors), TYPE(ref),
possible_keys(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 as an extra for the 50 rows that it selects and than orders by publication date does it do a filesort.
It creates a temporary table with 50 items. Which it then sorts with tablesort.
This has to be done this way, because MySQL cannot use the big index on those lonely 50 items, it would cost to much in IO-access time.
It's faster to to a sort on 50 numbers in memory then to access the index on disk.
You can do something to speed up the query though:
optimize table articles, articles_authors
and rerun the query.
EDIT: Speed up suggestion by denormalizing table articles
If you rewrite the query like this:
SELECT articles.id FROM articles WHERE articles.id IN (
SELECT articles_authors.fk_articles WHERE articles_authors.fk_authors = 586
LIMIT 0,50
)
ORDER BY articles.publicationDate;
You will probably see the same performance, but it highlights the problem. If author 586 has 180,000 articles, then MySQL has to search 50 items out of 180k in articles_authors and then search 50 items out of 180k again in the order table.
If you merge the tables article_authors and articles, your table articles will be denormalized (assuming an article can have multiple authors) but you don't have to do the join and you save yourself the 2nd 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
Now you can select from it like so
SELECT articles.id FROM articles WHERE articles.Author = 586
ORDER BY articles.publicationDate LIMIT 50,0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With