Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>First of all i would rewrite it as :</p> <pre><code>SELECT payments.method, payments.amount, payments.commission FROM payments LEFT JOIN member_to_group ON payments.member_id = member_to_group.member_id LEFT JOIN member ON member.member_id = member_to_group.member_id WHERE member_to_group.group_id = '37' AND payments.status = 'Active' AND member.is_root = 'No' </code></pre> <p>Then, try to compare the both queries using <strong><a href="http://dev.mysql.com/doc/refman/5.0/en/explain.html" rel="nofollow">EXPLAIN</a></strong> statement. This should show you where are the differences.</p> <p>Next i would take a look at this line <code>WHERE member_to_group.group_id = '37'</code> and make sure that i compare the columns against <code>INT</code> value instead of <code>VARCHAR</code> .. with all the fitting indexes.</p> <p>As for <code>payments.status</code> and <code>member.is_root</code> , both columns should be indexed (<em>AFAIK</em>, MySQL still does not support partial indexing).</p> <p>I am assuming that that following columns are primary/foreign keys and indexed accordingly:</p> <ul> <li><code>payments.member_id</code></li> <li><code>member_to_group.member_id</code></li> <li><code>member.member_id</code></li> </ul> <p>P.S. you could play around with order of <code>WHERE</code> statements , but i think that <strong>should</strong> be already handled by database's internal query optimization .. but who knows , it's mysql.</p> <hr> <p><strong>update</strong></p> <p>If the <code>EXPLAIN</code> says that you are not using indexes on <code>member_to_group</code> table, then you should check the indexes that both columns ( which are used in that query ): <code>group_id</code> and <code>member_id</code> .. at least one of them is <em>not indexed</em> in <code>member_to_group</code>.</p>
    singulars
    1. This table or related slice is empty.
    1. PONeed assistance optimizing query performance
      singulars
      1. PTQuestion
    1. PTAnswer
    1. UStereško
    1. UStereško
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PONeed assistance optimizing query performance
      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. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. CO+1 good answer for the better sql query
      singulars
      1. PO
      1. USSimon
    2. CONice. Is this functionally better or simply more legible?
      singulars
      1. PO
      1. UShumble_coder
    3. CO@humble_coder , that query is just "cleaning up" of your existing one. It **should not** function better. Then again, i have not really explored how mysql handles multiple tables in the `FROM` statement.
      singulars
      1. PO
      1. UStereško
 

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