MySQL Forums
Forum List  »  MyISAM

Mysql order by is very slow from 1,000,000 rows, we have nearly 300,000 search results
Posted by: Ozden Haldun
Date: November 23, 2018 07:29AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql order by is very slow from 1,000,000 rows, we have nearly 300,000 search results
107
November 23, 2018 07:29AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.