Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Right I've tried to do what I can. Mostly some reordering, some swapping to INNER JOINs and movement of WHERE syntax to the joins. Some renaming of aliases because you had used alias names matching table names negating their purpose.</p> <p>This will return rows where there are no matching transactions, so you might want to change that join to be INNER also, rather depends on the purpose / intended output, but this should be a good starting point. It allows MySQL to reduce the number of rows it looks at.</p> <p>Further improvement can be had by suitable indexes, but advising on these is hard without knowing data types / the variance in the data etc.</p> <pre><code>SELECT DISTINCT cnt.last_name, cnt.bus_ph_num, cnt.email_addr, u.login, cnt.fst_name, cnt.fk_id FROM contact cnt -- Changed to INNER JOIN as the WHERE indicates this is required INNER JOIN party p ON p.pk_id=cnt.fk_id -- Moved this limiting statement to the join AND p.fk_id= 'xyz' -- Changed to INNER JOIN as the LIKE % indicates htis is required INNER JOIN user u ON u.fk_id=cnt.fk_id -- OP added later, quite inefficient due to use of wildcards on both sides causing scans AND u.login LIKE '%somevalue%' -- Also here, INNER, as the WHERE means this is required INNER JOIN role r ON r.pk_row_id = u.role_id -- Moved this limiting statement to the join AND r.role_name ='ROLE_ADMIN' LEFT JOIN transaction tran ON tran.id=cnt.fk_id -- Moved this limiting statement to the join AND tran.txn_typ IN ( 1 , 2 , 3 ) -- OP added later, quite inefficient due to use of wildcards on both sides causing scans AND tran.txn_trk_num LIKE '%somevalue%' WHERE cnt.status_cd = 1 -- OP added later, quite inefficient due to use of wildcards on both sides causing scans AND cnt.last_name LIKE '%somevalue%' AND cnt.email_addr LIKE '%somevalue%' AND cnt.busin_ph_num LIKE '%somevalue%' </code></pre> <p>If you can get rid of those LIKE statements it will get better also. The following are able to use an index if a suitable one is available :</p> <ul> <li><code>... LIKE 'somevalue'</code></li> <li><code>... = 'somevalue'</code></li> <li><code>... LIKE 'somevalue%'</code></li> </ul> <p>However <code>... LIKE '%somevalue%'</code> is unable to use an index</p>
    singulars
    1. This table or related slice is empty.
    1. POCan we optimize this SQL Query?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSimon at mso.net
    1. USSimon at mso.net
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCan we optimize this SQL Query?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you very much :)
      singulars
      1. PO
      1. USuser2434
    2. COThat last LEFT JOIN probably should be an INNER JOIN, though that is me assuming you aren't interested in the result if there are no matching transactions :)
      singulars
      1. PO
      1. USSimon at mso.net
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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