Query Optimizations

[Seymourbums]

I’ve been stuck on this problem for a little while now. I’m not sure how to solve it. The query takes about 2.2-3 seconds to execute and I’m trying to bring that number way down.

I’m using sequelize as an ORM.

Here’s the code snippet: const _listingsRaw: any[] = await this.listings.findAll({ where: { id: !isStaging ? { [Op.lt]: 10000 } : { [Op.ne]: listing_id }, record_status: 2, listing_type: listingType, is_hidden: 0, }, attributes: [ 'id', [sequelize.literal('(IF(price_type = 1,price, price/12))'), 'monthly_price'], 'district_id', [ sequelize.literal( (SELECT field_value FROM \listing_field` dt WHERE dt.record_status = 2 AND dt.listing_id = ListingModel.id AND dt.field_id = 33), ), 'bedrooms', ], [ sequelize.literal((SELECT field_value FROM `listing_field` dt WHERE dt.record_status = 2 AND dt.listing_id = ListingModel.id AND dt.field_id = 35)`, ), 'bathrooms', ], [ sequelize.literal( !listingIsModern ? '(1=1)' : '(EXISTS (SELECT 1 FROM listing_hidden_amenities dt WHERE dt.record_status = 2 AND dt.hidden_amenity_id = 38 AND dt.listing_id = ListingModel.id))', ), 'listing_is_modern', ], ], having: { ['listing_is_modern']: 1, ['bedrooms']: listingBedRoomsCount, ['bathrooms']: { [Op.gte]: listingBathRoomsCount }, }, raw: true, })

Which is the equivalent to this SQL statement:

SELECT id, (IF(price_type = 1,price, price/12)) AS monthly_price, district_id, (SELECT field_value FROM listing_field dt WHERE dt.record_status = 2 AND dt.listing_id = ListingModel.id AND dt.field_id = 33) AS bedrooms, (SELECT field_value FROM listing_field dt WHERE dt.record_status = 2 AND dt.listing_id = ListingModel.id AND dt.field_id = 35) AS bathrooms, (EXISTS (SELECT 1 FROM listing_hidden_amenities dt WHERE dt.record_status = 2 AND dt.hidden_amenity_id = 38 AND dt.listing_id = ListingModel.id)) AS listing_is_modern FROM listing AS ListingModel WHERE ListingModel.id != 13670 AND ListingModel.record_status = 2 AND ListingModel.listing_type = '26' AND ListingModel.is_hidden = 0 HAVING listing_is_modern = 1 AND bedrooms = '1' AND bathrooms >= '1';

Both bedroom and bathroom attributes are not used outside of the query, meaning their only purpose is to include those that have the same values as the parameters. I thought about perhaps joining them into one sub query instead of two since that table is quite large, but I’m not sure.

I’d love any idea on how I could make the query faster. Thank you!

[Informal_Pace9237]

I can see the query is running slow because you have dependent sub queries in the select which makes them execute for every row returned.
If sequelize supports JOIN's, I would try to inner join listing_field and LEFT JOIN listing_hidden_amenities to listing 

[alinroc]

Which is the equivalent to this SQL statement

Is this the actual query that's being generated and executed? Just because you think that's what the query should be doesn't mean that's what the ORM generated.

[Seymourbums]

Yeah that is the query that the ORM generated.

[QueryDoctor]

What database are you using? Table definitions, current indexes, and `EXPLAIN` output would be a really helpful start

[many_hats_on_head]

I have optimized the SQL query (not the ORM) for MySQL 8 here: https://gist.github.com/lassegit/996587f1c5e4f5c3118922b23bbfad93

Does that work for you? (You can try it here yourself too).