Query in 2 Very Slow Tables in mysql

Asked

Viewed 42 times

-1

SELECT scanlators.* FROM lista_de_capitulos 
INNER JOIN scanlators ON JSON_CONTAINS(lista_de_capitulos.scanlator, scanlators.id) 
GROUP BY scanlators.url ORDER BY COUNT(lista_de_capitulos.id_manga) DESC LIMIT 100

How can I improve the execution speed of this query? It’s taking about 15 seconds to complete the execution currently.

mysql

  • Evaluate the implementation plan and if, by chance, the implementation of any index can improve the performance of your query.

  • this json_contains seems costly, I don’t understand why it’s doing so

  • @Lucasmiranda and because the column is an object.

  • but does it have to be? it will look at the whole object just to see if it has the id, this is nothing performatic, if you could normalize this guy in another table, you could index

1 answer

-1

It happens that this grouping the entire table, to then select the first 100 lines. You’d have to say you want to work the first hundred lines. I’m in real trouble and I don’t know the solution

  • 1

    And how would it be possible to select the 100 lines with the highest count without counting them all before?

  • I believe it is not possible to associate only 100 lines, because it needs to evaluate more than 50 thousand lines of chapters and 500 of scan lator

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.