Optimization query... 1

[Aerodyne]

Hi,

Just getting started with this (newbie)...
But I'd like to do a query that returns for each brewery, the beer that sold the most by value (revenue) in a given month. I also want it to include the name & addr of the brewery & the name and strength of the beer.

Reason for this is that I'm trying to optimize it to give the fastest results, I also want to compare a bunch of differant queries ... I don't want to use subroutines.

Could I plz have some examples + some tricks'n'tips for better results.

TIA,
Aerodyne

 

[hos2]

yes this is nice do we have to imagine the table relations ourselves ??

 

[Aerodyne]

Sorry ... will post tables & relations soon.
But general tips for the time being would do fine as well as pseudo queries.

Many thx.

 

[hvass]

My experience of query preformance in order of greatest effect:

1. Aggregate Tables
2. Query Optimisation
3. Physical Optimisation

1. Aggregate tables

Sounds like you know what question your users are going to ask so do the work for them. The ultimate might be a table with one row per brewery with the top seller for the period and all the details - you might end up with a v. small table. Obviously if you want them to have some query flexibility you might not be able to go this far.

2. Query Optimisation

Re-write the data so there are no joins or very few joins even at the expense of having relatively large physical table sizes. Try using enum and set data types. Make sure you do not use left joins, like, < and > use the alternatives ... write your where statements left to right (i.e. where the first term most restricts the number of qualifiying rows). Design your database to have small preferably numeric columns that are indexed, in general design out null values. Consider using SQL_BIG_RESULT if you are doing group bys. Consider sort by null if you don't need your group bys sorted. Lastly use Explain ... a lot

3. Physical Optimisation

Work out what your machine will actually have to do in terms of I/O. Minimise the physical size of your tables. You could consider HEAP tables or myisampack.
Then only at the end you might start changing the server setup.

Is that enough to be going on with - as the others have said give us a specific challenge and we may be able to help.

 

[Aerodyne]

Hello,

Here's what I've got so far:
Order 1 - m OrderItems
BeerPrices m - 1 OrderItems
Beers 1 - m BeerPacks
BeerPacks 1 - m BeerPrices

Order
*BreweryName
*CustName
*OrderNo
OrderType
OrderDate

OrderItems
*BreweryName
*CustName
*OrderNo
*BeerName
Qty

BeerPrices
*BreweryName
*BeerName
PricePerUnit

Beers
*BreweryName
*BeerName
ABV (Alcohol strength)

BeerPacks
*BreweryName
*BeerName
Size (eg 500 ml, 1 pt)

------
Feel free to amend the table as needed since it's just an experiment example for myself, but I'd like to keep the relationships as they are first & then tweak them.

TIA

 

[hvass]

Cut out the bits you dont need for analysis & reporting then aggregate to each reporting dimension for each fact- generalised as

create table AnalysisTable
select
sum(qty volume etc..) as fact
from all your tables .....
group by dimensions (e.g. brewery,beer,period)

Turn near continuous variables that will be used as dimensions into banded variables for example date
concat(year(OrderDate),month(OrderDate)) as period

Then I think you should be able to do it with one table

AnalysisTable table
period char(6)
Custid int(8) unsigned not null
Brewery enum(..,..,..,..,..,..,)
Beerid int(8) unsigned not null
qty int(8) unsigned not null (unless you can have negative quantity or returns)
volume int(8) unsigned (in litres?)

Index on each dimension - period,custid,breweryid,beerid

So say you had 3yrs of monthly data, 200 customers, av 5 breweries for each customer, av 10 beers per brewery that gives you max

36x200x5x10=360,000 records

Nominal size of record = 23 characters
period 6
Custid 4
Brewery 1
Beerid 4
qty 4
volume 4

Before you start to look at saving space ..

you could change period into an enum saves 5 characters, cut beerid down to a smallint saves 2 have volume in barrels saves 2

 

[Aerodyne]

Hi all,

hvass many thx for your reply.

I've only just started learning SQL in the last day or so, but I do understand DB & other langs ...

For some reason my query is not working corectly and I also just wanted to know is there more than one way to do this query in. i.e an efficient way (quicker) & a normal way (probably runs slower)? I have looked at using one table as well... AnalysisTbl, really helps but my queries are't working!!

TIA

 

[hvass]

Post the query that you cannot get to work and we will take a look at it

 

[Aerodyne]

I want the query to:
-Most sold beer in a given month by revenue.
-BreweryName & Addr that sold the most.
-The ABV (strength of the beer)

SELECT
BeerPrices.PricePerUnit,
Order.OrderDate,
Beers.ABV,
Brewery.BreweryName,
Brewery.Addr,
Order.OrderNo

FROM
BeerPrice, Order, Beers, Brewery

WHERE
???

And if I need to use the other SELECT statements?

---
I'm missing somthing here, plus not sure how to retrieve this info, all my efforts so far have been wrong & not even close. I know I'm suppose to multiply the PricePerUnit by somthing.
Also not sure where to add OrderNo to the query ... since I do need it to compute the most sold beer by revenue.

-I need to calculate the SUM of the orders for each beer then times them by the PricePerUnit, then show the month it was sold in, display the brewery that sells it + addr & the beers ABV form the Beers.ABV column.