CODEWITHSUNDEEP
mysqlperformanceyii2
jackall90
jackall90

Reputation: 11

The query with OR statement is too slow

this is my query

$select = (new yii\db\Query())
                ->select('autori.IDAutore, autori.IParte, autori.IIParte, autori.Prefisso, autori.Qualificazione, count(autori.IDAutore) AS qta')
                ->from('autori')
                ->innerJoin('bibliografie b', '
                b.RIDAutorePrinc1 = autori.IDAutore OR
                b.RIDAutorePrinc2 = autori.IDAutore OR
                b.RIDAutorePrinc3 = autori.IDAutore OR
                b.RIDAutorePrinc4 = autori.IDAutore OR
                b.RIDAutorePrinc5 = autori.IDAutore OR
                b.RIDAltroAutore1 = autori.IDAutore OR
                b.RIDAltroAutore2 = autori.IDAutore OR
                b.RIDAltroAutore3 = autori.IDAutore OR
                b.RIDAltroAutore4 = autori.IDAutore OR
                b.RIDAltroAutore5 = autori.IDAutore
            ')->limit(20)->orderby('COUNT(*) DESC');
$autoreList = $select->groupby('autori.IDAutore')->limit(20)->all();

The problem is that the loading page is too slow if the result have more than 2000 items. It take 20-30 seconds. Any suggestion for optimize the query? Thank you

Upvotes: 0

Views: 115

Answers (1)

Bruno
Bruno

Reputation: 101

I think based on your query that you should change db design , why you use so many columns ? Maybe i am wrong but you need to optimise this part , b.RIDAutorePrinc1 , b.RIDAutorePrinc2 ....... b.RIDAutorePrincN where you can have only one colum b.RIDAutorePrinc and put all values in this column and you dont have to put in a join so many or conditions .

Firstly join in sql always cost you some time and added here also so many or conditions the time goes up . If you put everything in b.RIDAutorePrinc you would have a shorter query like below and saving some appreciated time:

$select = (new yii\db\Query())
              ->select('autori.IDAutore, autori.IParte, autori.IIParte, autori.Prefisso, autori.Qualificazione, count(autori.IDAutore) AS qta')
              ->from('autori')
              ->innerJoin('bibliografie b', 'b.RIDAutorePrinc = autori.IDAutore')
              ->limit(20)     
              ->orderby('COUNT(*) DESC');

$autoreList = $select->groupby('autori.IDAutore')->limit(20)->all();

Upvotes: 1

Related Questions