In #2669962: Order of items during indexation, we started allowing you to configure the order in which things are indexed. This unintentionally caused a negative performance impact on the generated query due to having 2 sorts that could potentially have different orders.
To give some example numbers from MySQL:
EXPLAIN
SELECT *
FROM search_api_item sai
WHERE (index_id = 'community') AND (sai.status = '1')
ORDER BY sai.changed ASC, sai.item_id ASC
LIMIT 100 OFFSET 0;
yields Using where whereas
EXPLAIN
SELECT *
FROM search_api_item sai
WHERE (index_id = 'community') AND (sai.status = '1')
ORDER BY sai.changed DESC, sai.item_id ASC
LIMIT 100 OFFSET 0;
yieldsUsing where; Using filesort.
Running the actual queries takes ~2ms in the first case versus ~750ms in the second case.
By itself, this is fairly benign, but the query gets called many times when performing a full reindex, which can cause significant slowdown over the process as a whole.
| Comment | File | Size | Author |
|---|---|---|---|
| #2 | sort-order-performance-2943705-02.patch | 979 bytes | kevin.dutra |
Comments
Comment #2
kevin.dutra commentedThis change ensures the order on both sorts always remains the same. The secondary sort continues to provide predictability.
Comment #3
drunken monkeyHuh. Always more to learn regarding SQL performance optimization … Didn't know the order played a roll like this.
So, thanks a lot for reporting this, and providing a patch that already looks perfect!
However, testing this, I see "Using where; Using filesort" in all cases. Does this maybe only apply for larger result sets, do you know?
In any case, it would be good if someone else could weigh in here to make sure this is indeed an improvement.
However, I guess it can't really do any harm either way. Worst case, it will actually worsen performance in some cases and we'll have to roll it back. But it shouldn't break anything.
Comment #4
borisson_I don't see the using filesort for the first one, but I do see if for the second query.
I have tried testing this by adding 5000 nodes to an index, then running both queries from a testscript. I used ab to benchmark the results, because I don't really know a better way. I changed both queries to use
SELECT SQL_NO_CACHE *The differences were not big:
Requests per second: 11.42 [#/sec] (mean)
Time per request: 87.587 [ms] (mean)
Requests per second: 11.34 [#/sec] (mean)
Time per request: 88.186 [ms] (mean)
However, I'm sure that this was not a good way to test this. And I'm all for this change. Setting to rtbc based on no longer using seeing "Using filesort"
Comment #5
kevin.dutra commentedOops, yes I totally forgot to mention the size of the table. Ours is definitely on the larger size: ~1.5M rows. At that size, the difference starts to become more noticeable.
Comment #7
drunken monkeyThanks for testing, Joris!
If you're for this, too, and do see the improvement: great, then let's do this.
Committed.
Thanks again, Kevin!