CODEWITHSUNDEEP
mysqloptimization
Genioblu
Genioblu

Reputation: 51

MySQL QUERY with 4 tables; is there a way to make it faster and use less resources?

just a question about MySQL, to speed up the query.

I have 4 tables:

- PROD_sell (744 rows):
PROD_SELL_id | USER_id | PROD_sell_crediti | PROD_SELL_datepay |
 - PROD_newsletter (7300 rows)
USER_id | NEWSL_ref_area | NEWSL_ref_subprod | USER_realcf_area | USER_realcf_page_num | 
  - PROD_archivio (197 rows)
PROD_ARCH_id | PROD_ARCH_hidden | PROD_CAT_id |
 - PROD_cat (3 rows)
PROD_CAT_id | PROD_CAT_title |

This is my query (via php):

SELECT COUNT(DISTINCT s.PROD_SELL_id) AS count,
c.PROD_CAT_title
FROM PROD_sell AS s, PROD_newsletter AS n, PROD_archivio AS a, PROD_cat AS c
WHERE
s.USER_id = n.USER_id
AND 
((n.NEWSL_ref_area = 2 AND n.NEWSL_ref_subprod = a.PROD_ARCH_id) 
OR 
(n.USER_realcf_area = 'archivio' AND n.USER_realcf_page_num = a.PROD_ARCH_id))
AND a.PROD_ARCH_hidden = '0'
AND a.PROD_CAT_id = c.PROD_CAT_id
AND s.PROD_sell_crediti = '0' AND s.PROD_SELL_datepay >= '$data_inizio_where' AND s.PROD_SELL_datepay <= '$data_fine_where'
GROUP BY a.PROD_CAT_id ASC
ORDER BY count DESC

The result is (for example):

CAT 1: 30 sales
CAT 2: 23 sales
CAT 3: 31 sales
and so on...

I know it looks orrible, it works but I need a faster way to make the same query, beacuse it takes a lot of resources and the site goes down.

The worst part, as you can guess, is:

AND 
((n.NEWSL_ref_area = 2 AND n.NEWSL_ref_subprod = a.PROD_ARCH_id) 
OR 
(n.USER_realcf_area = 'archivio' AND n.USER_realcf_page_num = a.PROD_ARCH_id))

Is there a way to make it faster? How would you write the whole query? I'm not so good with SQL optimization :-)

Thanks

Upvotes: 1

Views: 35

Answers (1)

Barmar
Barmar

Reputation: 780724

That OR condition cannot use an index, so it slows down the query.. The workaround is to split your query into two queries, which are identical except that they each have a different condition from the OR. Then combine them using UNION.

And of course, make sure you have indexes on all the relevant columns.

Upvotes: 1

Related Questions