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

New Topic

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

Navigate: Previous Message• Next Message

Options: Reply• Quote

Subject

Views

Written By

Posted

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

1236

Ozden Haldun

November 23, 2018 07:29AM

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

739

Peter Brawley

November 23, 2018 11:50AM

Sorry, you can't reply to this topic. It has been closed.

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.