Re: Creating Sort Index Slow

[Rick James]

> state SENDING DATA become super slow.

That "state" is essentially useless information. It is saying that the query is hard to optimize and is taking a long time.

Leading wildcards (%) are essentially unoptimizable. This index _might_ help:
INDEX(type, wos_date, refno)

EXPLAIN SELECT ...;
would provide more info for us to help you with.

Do you really need "*"? Or do you need only a few of the fields? Specifying only the needed fields _may_ speed it up. With "SELECT *", it will collect all the columns (and you have a lot!) into a temp table. This temp table will be big and either in memory or on disk. In either case it will be slow because of the bulkiness.

Again, the subquery trick I mentioned will _probably_ help in this query.