ansaurus

Question

How can I optimise this query in MYSQL? What needs to be done

Answer 1

A:

Sorting and fetching that amount of records should not be a problem for MySQL. As I can see from your print screen not all tables have indexes. Create Index for each field that involved with JOIN or WHERE statements. And overall performance should increase.

Nazariy 2010-08-19 01:46:40

Joining using a function--IE `UPPER(a.col) = UPPER(b.col)`--will not use a index if there is one on `a.col` or `b.col`...

OMG Ponies 2010-08-19 02:18:38

Answer 2

+1 A:

More of a detailed question...

From what I'm reading in the query, you are doing all left joins ... thus indicating all records from the left side table, regardless of there being a match in the right-side table based on its join. In this case, the inner most from...

ed_abc_publication_audits left-joined to public_publications

yet your where clause is qualifying a value on BOTH sides via

(a.audit_active = 1) and (p.pub_unfinancial = 0)

To me, that would imply an INNER JOIN where the record MUST exist on each side...

You then do LEFT JOINs to auau7859_aba.ed_aba_frequencies, ed_abc_publicationtypes, ed_abc_audittypes and Members....

Are you expecting some non-matches at these deeper level joins and thus expecting NULL values, or is what you want is where they ALL ultimately join together at all levels.

HOWEVER, it looks like the left joins to the other tables are more of a lookup/reference table where the record should ALWAYS exist.. Such as

Each publication audit will ALWAYS have a valid "Audit Type"

Each publication will ALWAYS have a "Frequency ID", "Type of Publication" and "Member"

Is this correct in my interpretation??? The lookup tables will ALWAYS have matching record? If so, which ones... if Optional which ones...

Based on your answers, try the following query... The STRAIGHT_JOIN can be powerful if you know your data, relationships and can optimize on your own which is the key table driving the rest of the system...

SELECT STRAIGHT_JOIN
        a.*,
        p.*
    FROM 
        auau7859_aba.ed_abc_publication_audits a
            left join auau7859_aba.ed_abc_audittypes atypes
                ON a.audit_type = atypes.audittypeid,
        auau7859_aba.public_publications p 
            left join auau7859_aba.ed_aba_frequencies f
                ON p.pub_frequencyid = f.frequencyid
            left join auau7859_aba.ed_abc_publicationtypes t
                ON p.pub_type = t.publicationtypeid
            left join auau7859_aba.Members m 
                on p.pub_memid = m.MemID
    where 
            a.audit_pubid = p.pubid
        and a.Audit_Active = 1
        and p.pub_unfinancial = 0

DRapp 2010-08-19 15:00:33

Hey D-Rapp... that's good advice, you make a good point.I changed the join to an inner join and added the condition to the join itself, like so:`auau7859_aba`.`ed_abc_publication_audits` `a` inner join `auau7859_aba`.`public_publications` `p` on ( (`p`.`pubid` = `a`.`audit_pubid` and pub_unfinancial=0) )The query time went from 0.437 to 0.156, a massive improvement. Answers to your question below

Jason 2010-08-19 23:57:51

The query def works like this:Publications have many Circulations, some publications are not valid (unfinancial, i know the naming is so stupid, sorry i didn't design that). All circulations relate to one publication. These are the main two tables. The rest of the tables are simply, as you mentioned, lookup tables. and if they don't have a value then they can just return null, as they do.So your interpretation is correct. required tables are: Publications / Circulations. optional tables: Audit Type, Frequency, publication type and Members.

Jason 2010-08-20 00:01:10

Hey D-Rap, that query works well, i never knew about straight joins so I'm reading up about them now.This hasn't solved my problem, but it's brought me much closer. I realise now, your query doesn't use the filesort when I run it (btw it runs well, good work!) but as soon as I add my select clause to it, it goes back to being slow as a dog. So I think I need to post my select clause and get advice on what is making it so slow...

Jason 2010-08-24 23:25:55

@Jason When you add WHAT select clause to it...

DRapp 2010-08-25 01:48:47

Answer 3

A:

If you want to avoid the filesort, put the fields in your ORDER BY in an index you can use.

In your case, a.audit_anps isn't available in an index, so the filesort kicks in. Put an index on (pubid, audit_anps) and it will do what you want.

Be mindful when adding indexes, though; each index will have to be updated on every write to, so simply throwing indices around may result in beautiful EXPLAINs but horrible app-performance. Remember: EXPLAIN only tells you what the optimizer does with your query and a given set of tables and indexes. It can help you achieve good app performance, but a nice looking EXPLAIN is not your real goal.

CharString 2010-08-22 09:50:26

Hi CharString,I've added an index of orderby(audit_Pubid, Audit_anps)it is still doing the filesort though:|| *id* || *select_type* || *table* || *type* || *possible_keys* || *key* || *key_len* || *ref* || *rows* || *Extra* |||| 2 || DERIVED || a || ALL || pubid,orderby || _NULL_ || _NULL_ || _NULL_ || 4659 || Using filesort ||I went a bit crazy and then added audit_anps, audit_pubid to indexes as well. Still no dice with this. The query is still running too slow. Thank you so much for your help, I feel like we are close but not there yet.

Jason 2010-08-23 23:52:33

Look I totally understand that updates will take much longer, but to be honest this data changes only 4 times per year. It's worth the trade of in update speed if I can get this query going. I'm going to post some more details in the question.

Jason 2010-08-23 23:53:18

Answer 4

A:

To get the value for audit_parentpublication@display, it's doing a query on auau7859_aba.public_publications for every row.

Try modifying your main query to outer join auau7859_aba.public_publications instead.

grossvogel 2010-09-03 21:18:45

ansaurus

tags:

views:

answers:

How can I optimise this query in MYSQL? What needs to be done

Please scroll down to the "25/08/2010 Update".

related questions