CODEWITHSUNDEEP
mysqlsqldatabase
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Query Performance: Search by postcode, company name or town

See the SQL query below - it allow you to search the Shop by Postcode, Company Name or Town (location)... On the frontend website there will be only one search textbox without dropdown search type.

It will only show the result if shop_options.live is equal to 1 and depending which day shop is open: O_Hour.weekday = '5' (Friday).

If I search by S_D.postcode (for example: S_D.postcode = 'L14') it will then find L14 from shop_delivery_area table and then display list of shops from that postcode.

SELECT distinct S.*, S.company, S.street, S.town FROM shop as S
    JOIN shop_delivery_area as S_D on S_D.shopID = S.shopID
    JOIN shop_options on shop_options.shopID = S.shopID
    JOIN shop_opening_hours as O_Hour on O_Hour.shopID  = S.shopID
WHERE (S_D.postcode = 'Liverpool'  OR S.company LIKE 'Liverpool' OR S.town LIKE 'Liverpool') 
AND shop_options.live = '1' AND O_Hour.weekday = '5' 
    ORDER BY O_Hour.opentime

The query does work but its very slow. Almost a second to get the result. How to improve the performance faster?

Edit: Fixed SQL query.

Upvotes: 0

Views: 195

Answers (3)

Tom Mac
Tom Mac

Reputation: 9853

If you need to keep the predicates i.e.

S_D.postcode = 'Liverpool'  OR S.company LIKE 'Liverpool' OR S.town LIKE 'Liverpool'

Then consider adding indexes on the same columns. So:

ALTER TABLE shop_delivery_area ADD KEY `sda_idx1` (`postcode`);
ALTER TABLE shop ADD KEY `shop_idx1` (`company`);
ALTER TABLE shop ADD KEY `shop_idx2` (`town`);

One other point is about fuzzy searching. If you can replace the 'LIKE' with an '=' then you'll see a speed increase. There's not much point using 'LIKE' with no fuzzy searching though i.e. LIKE 'Liverpool'. Use either LIKE '%Liverpool%' or = 'Liverpool'. So either use:

    S_D.postcode = 'Liverpool'  OR S.company LIKE '%Liverpool%' OR S.town LIKE '%Liverpool%'

or

    S_D.postcode = 'Liverpool'  OR S.company = 'Liverpool' OR S.town = 'Liverpool'

If you use the latter and create the indexes then your query should run just fine!

Upvotes: 1

matino
matino

Reputation: 17725

  1. Get rid of conditions for empty strings (S_D.postcode = '' OR S.company LIKE '')
  2. Generally LIKE will slow down your queries a lot, so I'll avoid it whenever it's possible
  3. Add indexes on WHERE columns

Upvotes: 0

michael667
michael667

Reputation: 3260

The use of IFNULL instead of OR may provide substantial performance improvements, depending on your amount of data, indexes, etc.

Upvotes: 0

Related Questions