Advice on optimising query for a beginner please!

[longcroft]

Hi all

I am really new to mySQL, and have just completed my first web app using mySQL instead of Access. The problem is that users are reporting that the app is running very slowly and I suspect it may be something to do with the following query:

<cfquery name=&quot;GetOffers&quot; datasource=&quot;#ThisDSN#&quot;>
SELECT products.ID AS PartID,
products.PartCode AS PCode,
products.Msg AS Msg,
offers.ID AS ID,
offers.OfferCode AS OfferCode,
offers.PriceBand AS PriceBand,
offers.PartTitle AS PartTitle,
offers.PricePrefix,
products.PartCode AS PartCode,
products.FreeText AS FT,
Left(offers.Description,#NoChars#) AS PartDesc
FROM products
LEFT JOIN offers ON products.ID = offers.PartID
LEFT JOIN publications ON publications.ID = offers.PubID
LEFT JOIN brands ON brands.ID = publications.BrandID
LEFT JOIN sites ON sites.ID = brands.SiteID
LEFT JOIN categories ON categories.ID = products.CatID
LEFT JOIN sections ON sections.ID = categories.SectionID
<CFIF URL.NEW is 1>
WHERE (StartDate >= #CREATEODBCDATETIME(DayB4Yesterday)#
AND EndDate > #CREATEODBCDATETIME(REQUEST.TODAYSTART)#)
OR products.New = 1
<CFELSE>
WHERE (StartDate <= #CREATEODBCDATETIME(REQUEST.TODAYSTART)#
AND EndDate > #CREATEODBCDATETIME(REQUEST.TODAYSTART)#)
</CFIF>
<CFIF REQUEST.EXPRESSION IS NOT &quot;&quot;>
AND (
offers.OfferCode LIKE '%#REQUEST.EXPRESSION#%'
OR offers.Description LIKE '%#REQUEST.EXPRESSION#%'
OR offers.PartTitle LIKE '%#REQUEST.EXPRESSION#%'
)
</CFIF>
AND offers.DELETED = 0
AND sites.ID = #Request.SITE#
AND brands.ID = #REQUEST.BRAND#
<CFIF URL.SECTION GT 0>
AND sections.ID = #URL.SECTION#
</CFIF>
<CFIF URL.CATEGORY GT 0>
AND categories.ID = #URL.CATEGORY#
</CFIF>
<CFIF FA IS &quot;SPECIALS&quot;>
AND offers.PriceBand = 3
</CFIF>
GROUP BY products.ID
ORDER BY products.PartTitle
</cfquery>

Huge isn't it?!

If anyone can see any immediate problems with it, I would be really grateful for any advice on how I can optimise it to make the app run a bit more quickly.

TIA for your help guys!

 

[hvass]

OK - I will take the risk that this is not a joke and here is some FWIW advice but no easy answers.

You could start by reading the optimisation section of the manual but I am afaid you need more major surgery than it can offer - start by running explain select ...... on it and see what the product of all the joins is then convince yourself to start again.

Try and avoid table joins, especially left joins. Avoid LIKE expressions use regular expressions (only time I might use a Like is if I am doing something with the % wild character at the end &quot;SOMETHING%&quot; and the field is indexed).

I can only guess at your application but my guess is the only way you are going to be able to do that is probably start again with your data design.

Instead
Re-design your data for performance not data storage: you look like you are linking sections-categories-products-offers-publicatonis-brands-sites : why not build a offers2 table of uniqueoffers being offers within publication within brands within sites then inner join to products2 being products with categories and sections as additional variables in the table. Then index and inner join on productID.

Redesign your tables to be physically small consider using enum and set data types (bit_and and bit_or functions are very fast), make sure your on, where, group by etc. benefit from indexes.

My experience is you can always get performance through data design but if you really get the bug and want to go further and the data is relatively static and can be 'released' or updated periodically (perhaps overnight or hourly) then consider physically sorting records, myisampack or holding the tables in memory using temporary tables.

OK so it was either a wind up or you probably did not understand a word of this.

 

[longcroft]

Thanks hvass ... i wish it was a wind up, but it's not (i did say i'm a beginner!). I wouldn't go so far as to say I didn't understand a word of your answer, but I can see I've got a long way to go to get this right. The query does work, but your answer does explain to me why it's so slow.

Any suggestions for some resources that could help me get started? Of course there's the mysql manual, but i think something a little more tutorial-like would perhaps help me more to get started.

Thanks again for taking the time to look.

 

[longcroft]

hvass - i have now restructured the database and eliminated the need for joins in that query, and it's running fine now. Learnt that lesson the hard way - thanks for your advice

 

[hvass]

glad you got it sorted

reading my post again - appologies for my suspicions & tone

I always try and answer a few posts when I have found something useful through the forum as a way of giving something back to the battered IT community - but there is always a suspicion that you might be doing some students homework for them hence sometimes answers can be a bit cagey - I will put my paranoia back in its box