creating Views  in MYSQL SERVER getting to much  slow down for  more then  twenty thousand records

kausar ali
kausar ali used Ask the Experts™
on
creating View  in MYSQL SERVER getting to much  slow down for  more then  twenty thousand records.
The view have been created in mysql. But when i click on the browse then it getting  slow down . main issue is that how can i handle large records in mysql views. Aactually i am converting Ms Access Database into mysql . there is lot of queries in Aaccess DB that i have to need use as view in mysql . but for large getting queries when i use in mysql for view then it gett very slow .


here is the example of query that is used for creating view in mysql server.it is fetching twenty thousand records.

SELECT FormulaInfo.FormulaInfoID, (Sum(formuladetail.Qty*fragrance.cost*unittable.unitconverter/`unittable_1`.unitconverter)/totalqty) AS UnitCost,
 Format(Sum(formuladetail.Qty*fragrance.previouscost*unittable.unitconverter/`unittable_1`.unitconverter)/totalqty
, 'Currency') & ' / ' & `unittable_1`.unitcode AS UnitPCost
FROM (UnitTable INNER JOIN Fragrance ON UnitTable.UnitID = Fragrance.Unit) INNER JOIN
(((FormulaInfo INNER JOIN FormulaDetail ON (FormulaInfo.FormulaID = FormulaDetail.FormulaID) AND (FormulaInfo.Revision = FormulaDetail.Revision)) INNER JOIN UnitTable AS UnitTable_1 ON FormulaInfo.Unit = UnitTable_1.UnitID) INNER JOIN Qry_FormulaList_TotalQty ON FormulaInfo.FormulaInfoID = Qry_FormulaList_TotalQty.FormulaInfoID) ON Fragrance.FragranceID = FormulaDetail.FragranceID
GROUP BY FormulaInfo.FormulaInfoID, Qry_FormulaList_TotalQty.TotalQty, UnitTable_1.UnitCode;



Thanks in Advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Converting to a server-based system does not guarantee better performance, and often you see worse performance when doing this.

The first place to look is indexing on the MySQL machine - make sure any columns used in a Join or Where clause are indexed (within reason, of course - there's no reason to index a Boolean field, for example).

Remove the Formatting processes in the View. Do that on UI instead, where it's intended to be done.
Distinguished Expert 2017

Commented:
A major benefit of using a server based RDBMS is that you get to send it a query with a where clause and get back ONLY the requested rows.  I'm not sure how MySQL works but SSMS (SQL Server Management Studio) limits the rows returned when you open a table in data sheet view to 1000.  Granted you can change that but the idea is that you probably don't need to bring down all the rows in a table.  The logic follows here.  When you are designing forms and queries you should always include criteria that will limit the rows returned.  Most of my forms have some filtering fields in the form header.  Sometimes they are combos and sometimes they are text fields but in all cases the query bound to the RecordSource references them so my forms always open "empty"

Select ... From ... Where fldA = Forms!myform!fldA or fldB = forms!myformfldB;

However, 20,000 is pretty small in the greater scheme of things.  Is the slowdown being caused by network speed? or perhaps MySQL throttles the performance if you are not using a paid version the way SQL Server Express does.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That because you:

1. Inner Join on a local query:

    INNER JOIN
        Qry_FormulaList_TotalQty
        ON FormulaInfo.FormulaInfoID = Qry_FormulaList_TotalQty.FormulaInfoID

That will pull the entire table from MySQL. Replace the query with a view created in MySQL to avoid that.

2. Use an Access function:

    Format(Sum(formuladetail.Qty* ...

Again, that will pull the entire table from MySQL.

So, remove that and create a local query that uses the query below (without Format) as source. Create the expression using Format in the new local query.

SELECT 
    FormulaInfo.FormulaInfoID, 
    (Sum(formuladetail.Qty*fragrance.cost*unittable.unitconverter/`unittable_1`.unitconverter)/totalqty) AS UnitCost,
    Format(Sum(formuladetail.Qty*fragrance.previouscost*unittable.unitconverter/`unittable_1`.unitconverter)/totalqty 
, 'Currency') & ' / ' & `unittable_1`.unitcode AS UnitPCost
FROM 
    (UnitTable 
INNER JOIN 
    Fragrance 
    ON UnitTable.UnitID = Fragrance.Unit) 
    INNER JOIN 
    (((FormulaInfo 
    INNER JOIN 
        FormulaDetail 
        ON (FormulaInfo.FormulaID = FormulaDetail.FormulaID) AND (FormulaInfo.Revision = FormulaDetail.Revision)) 
        INNER JOIN UnitTable AS UnitTable_1 
        ON FormulaInfo.Unit = UnitTable_1.UnitID) 
        INNER JOIN 
        Qry_FormulaList_TotalQty 
        ON FormulaInfo.FormulaInfoID = Qry_FormulaList_TotalQty.FormulaInfoID) 
    ON Fragrance.FragranceID = FormulaDetail.FragranceID
GROUP BY 
    FormulaInfo.FormulaInfoID, 
    Qry_FormulaList_TotalQty.TotalQty, 
    UnitTable_1.UnitCode;

Open in new window

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial