Link to home
Start Free TrialLog in
Avatar of dannyg280
dannyg280Flag for United States of America

asked on

Help With Query That Causes "WARNING: MySQL server has gone away" Error

I get the error in the title when I try to run this query below. I am on a 1&1 shared hosting server, running the query through PHPMyAdmin, which I have no control of the PHPMyAdmin config settings. I realize the error means it's timing out, however this query is not that complex so I don't see where the problem is.

I'm hoping there's a glaring error in the query that is causing the performance hit (or loop) so I will just post the query for now. If the individual table structure is needed please let me know

Select it.Category
, it.CategoryDescription
, c.AvgTop150 as OrginalCatAvg
, ROUND(SUM(CASE WHEN InvoiceDate Between '2016-1-1' and '2016-12-31' THEN PubProfit ELSE 0 END) / 150, 2) as CalendarCatAvg
From vCustomerInvoiceDetail i
Left Outer Join InventoryItems it on i.ItemId = it.ItemID
Left Outer Join Categories c on it.Category = c.CatID

Where CustID in (Select CustID from CustRank where rank15 < 151)
and InvoiceDate between '2016-1-1' and '2016-12-31'
Group by it.Category, it.CategoryDescription, AvgTop150

Open in new window

The vCUstomerInvoiceDetail Table contains a row for every line of an invoice, this is the largest table with about 50k entries, however limiting it to 1 year only means about 9k entries
The InventoryItems Table includes info about the items including their current category.
The Categories Table includes category info including the average profit from the top 150 customers

ALl other queries I've run are coming back fine but this one keeps timing out... any idea where to look?
ASKER CERTIFIED SOLUTION
Avatar of Kim Ryan
Kim Ryan
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dannyg280

ASKER

Yes, indexing helped a great deal. Still takes a long time but it does complete.