Search slower again

Developer comments

This comes directly as a result of the changes in [12975, this issue].

Might be related in changes of charset and collation.

This is unrelated to the charset and table schema changes. When running a store in two languages, english and german, it affects only english. The english query takes 1.9 seconds, the same statement using the german labels takes 0.3s. Very strange and hard to trace down.

The query optimizer makes the choses the wrong execution plan. Not sure why he messes up, but messing up he does. In his defense: it seems that he gets fed wrong stats.

This affects large systems only (high topic count, high domain topic type count). Best strategy seems to the straight join. There is only two options select type first or full table scan the search term. The full table scan is constant but might slow search down for single type searches when there is only a few instances.

Reporting date

2024-12-02

Reported by

Robert Cerny

Resolving date

2024-12-11

Resolved by

Robert Cerny

Resolved in version

Topincs 10.0.0

Related to

Refactor search(Change)

Caused by

Label table per language(Change)

Work sessions2

13712

13713

Helpful webpages4

www.percona.com/…act-on-mysql-performance/

dev.mysql.com/…4/en/optimizer-tracing.html

dba.stackexchange.com/…-bad-execution-plan

medium.com/…rring-order-index-e974c07c77bc