Databases
StackOverflow2013
Postsdbo
POMySQL optimise triple left join
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL optimise triple left join
    text
    copied!<p>I need to make a query that gets columns from various tables - 5 tables in total. I am happy to be getting the gist of left joins, and getting the results I need, the only problem is the speed. I am a bit lost on indexes/keys, possibly because my case is atypical to all the examples I am reading online. The "unique" id across my tables is not a primary key in all of tables and cant be because that id can have several rows in the table (historical/audit purposes). The id is also in different formats across a few tables (##/### and ##-###) so the function to match them might be causing the big slow up. It may be easier to paste my code so far (only 3 tables - not doing all of them until I can conquer 3 first!), and someone can hopefully suggest how to improve with indexes and keys. </p> <pre><code>SELECT dc.URN,dc.GuideName,dc.GuideStreet,ss.categorycalc,pm.initiatedate FROM dc LEFT JOIN pm ON dc.URN = pm.URN LEFT JOIN ss ON dc.URN = replace(ss.URN,"/","-") WHERE dc.GuidePCode LIKE 'WA9%' ORDER BY pm.Status ASC; </code></pre> <p>I would appreciate less advice to just start the database all over and 'do it properly' etc. - there are a lot of programs already coded to insert data to certain tables in certain ways. So it is quite impractical to change the structure of the unique id, and the way it is being written to certain tables. I just need to focus on pulling off this report of the data that I have already got. The "WA9" part will change depending on what the user wants to search for.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload