CODEWITHSUNDEEP
mysqlsql
Ivan Off
Ivan Off

Reputation: 25

Order by count(*) of my second table takes long time

Let's assume I have 2 tables. One contains car manufacturer's names and their IDs, the second contains information about car models. I need to select few of them from the first table, but order them by quantity of linked from the second table data.

Currently, my query looks like this:

SELECT DISTINCT `manufacturers`.`name`, 
                `manufacturers`.`cars_link`, 
                `manufacturers`.`slug` 
FROM   `manufacturers` 
       JOIN `cars` 
         ON manufacturers.cars_link = cars.manufacturer 
WHERE  ( NOT ( `manufacturers`.`cars_link` IS NULL ) ) 
       AND ( `cars`.`class` = 'sedan' ) 
ORDER  BY (SELECT Count(*) 
           FROM   `cars` 
           WHERE  `manufacturers`.cars_link = `cars`.manufacturer) DESC

It was working ok for my table of scooters which size is few dozens of mb. But now i need to do the same thing for the cars table, which size is few hundreds megabytes. The problem is that the query takes very long time, sometimes it even causes nginx timeout. Also, i think, that i have all the necesary database indexes. Is there any alternative for the query above?

Upvotes: 0

Views: 36

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13016

lets try to use subquery for your count instead.

select * from (
    select distinct m.name, m.cars_link, m.slug 
    from manufacturers m
    join cars c on m.cars_link=c.manufacturer
    left join
        (select count(1) ct, c1.manufacturer from manufacturers m1 
            inner join cars_link c2 on m1.cars_link=c2.manufacturer
            where coalesce(m1.cars_link, '') != '' and c1.class='sedan'
            group by c1.manufacturer) as t1
            on t1.manufacturer = c.manufacturer
    where coalesce(m.cars_link, '') != '' and c.class='sedan') t2
order by t1.ct

Upvotes: 1

Related Questions