StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery optimization order by
text
Body
copied!<p>I have two tables</p> <ol> <li>LangArticles | columns: id (INT) ,de (VARCHAR),en (VARCHAR),count_links(INT)</li> <li>WikiLinks | columns: article_id,link_id,nr_in_article (all integer)</li> </ol> <p>The name of an article is in the columns de (German) and en (English). The id in the LangArticles table is the same as the ids article_id and link_id.</p> <p>I want now to get all article names which links to another article. So I want all articles which links to 'abc'. 'abc' has the id = '1'</p> <p>So my normal query (without an order by) looks like:</p> <pre><code> select distinct(LA.de),W.nr_in_article,LA.count_links from LangArticles as LA inner join WikiLinks as W on W.article_id = LA.id where W.link_id in ("1") </code></pre> <p>This maybe took 0.001 seconds and give me 100000 results. Actually I want the best 5 hits. Best means in this case the most relevant ones. I want to sort it like this: The articles which links to 'abc' at the beginning of an article (nr_in_article) and which has a lot of links itself (count_links) should have a high ranking.</p> <p>I am using an </p> <pre><code>order by (1-(W.nr_in_article/LA.count_links)) desc </code></pre> <p>for this.</p> <p>The problem is that I am not sure how to optimize this order by.</p> <p>The Explain in mysql says that he has to use a temporary file and filesort and can't use the index on the order by keys. For testing I tried an "easy" order by W.nr_in_article so an normal order with one key. </p> <p>For your information my indices are:</p> <p>in LangArticles: id (primary),de (unique),en (unique), count_links(index)</p> <p>in WikiLinks: article_id(index),link_id(index),nr_in_article(index)</p> <p>But I tried this two multiindices link_id,nr_in_article & article_id,nr_in_article as well.</p> <p>And the query with order by tooks approximately 5.5 seconds. :(</p> <p>I think I know why MySql has to use a temporary file and filesort here because all 100,000 entries has to be found with one index (link_id) and afterwards it has to be sorted and in a temporary file it can't use an index. </p> <p>But is there any way to make this faster? Actually I only want the best 5 hits so there is no need to sort everything. I am not sure if sth. like the bad sort (bubble sort) would be faster for this than Quicksort which sorts the hole temporary table.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload