Mysql order by is very slow from 1,000,000 rows, we have nearly 300,000 search results

124 Views Asked by At

When we do Natural Search in Mysql from 1,000,000 rows, we have nearly 300,000 search results. This query lasts 7-8 seconds when we want to select the first 2,000 documents with the highest natural score.

This time is too long for us. How can we shorten the duration of the first 2,000 pick by sort by natural rating?

Thank you in advance for your help.

select (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc' in natural LANGUAGE mode)) as n from ictihat 
 where   (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc +Deyn +Vecibe +Evam +Liability +Kredi +Ödev +Ödünç +Namus +borcu +Takanak +Medyun +Debitor +Zimemdar +Yükümlü +Verecekli +Bölük +Çember +Çevre +İdare +Menzil +Ofis +Temşiyet +Yürütme +Curator +Kayyum +Yeğleme +Salahiyettar +Salâhiyyetdâr +Salahiyetli +Mezun' in natural LANGUAGE mode))
 **order by n desc** LIMIT 0,2000

Result: 4.703 sec

Explain:

1 SIMPLE ictihat fulltext aramafull aramafull 0 const 1 100.00 Using where; Using filesort

 select (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc' in natural LANGUAGE mode)) as n from ictihat 
 where   (match(baslik,detay) against('+borçlu +yetkili +icra +dairesi +tercih +kayyım +borç +daire +borc +Deyn +Vecibe +Evam +Liability +Kredi +Ödev +Ödünç +Namus +borcu +Takanak +Medyun +Debitor +Zimemdar +Yükümlü +Verecekli +Bölük +Çember +Çevre +İdare +Menzil +Ofis +Temşiyet +Yürütme +Curator +Kayyum +Yeğleme +Salahiyettar +Salâhiyyetdâr +Salahiyetli +Mezun' in natural LANGUAGE mode))
 LIMIT 0,2000

Result: 1.005 sec

Explain: 1 SIMPLE ictihat fulltext aramafull aramafull 0 const 1 100.00 Using where

0

There are 0 best solutions below