StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Is there an index on <code>appointmentstatusmst.entityid</code>? How about on <code>table1.shdclientid</code> and <code>table1.shdclientdcnid</code>? How about on <code>hlcclientdetails.cldregno</code> and <code>hlcclientdetails.clddcnid</code>?</p> <p>For your joins to work smoothly, you need indexes on the columns involved in the join criteria as well as on the column you're using to choose output rows.</p> <p>@Bohemian is correct, this is a medium-sized, not large, problem.</p> <p>EDIT.</p> <p>It may help to think through what your mySQL server actually has to do to perform your query. </p> <pre><code> where hlcclientdetails.cldunifiedcrmid = ? </code></pre> <p>means it has to romp through your <code>hlcclientdetails</code> table looking for certain rows. So an index on <code>cldunifiedcrmid</code> will let it do that efficiently. You've mentioned that you have that index in place. Good.</p> <p>Moving on,</p> <pre><code> inner join hlcclientdetails on a.shdclientid = hlcclientdetails.cldregno and a.shdclientdcnid = hlcclientdetails.clddcnid </code></pre> <p>means it has to take the rows it found in your <code>hlcclientdetails</code> table and match them to your <code>table1 a</code> by looking at <code>a.shdclientdcnid</code> and <code>a.shdclientid</code>. So, your query can probably benefit from a compound index in <code>table1 a</code> for those two columns. You should try adding that index, and see whether performance gets better.</p> <p>Finally, </p> <pre><code>inner join appointmentstatusmst st on st.entity_id = a.shdstatuslviid </code></pre> <p>means the server has to take the rows it found in <code>table1 a</code> and match them up to this third table. You mentioned in your comment that entity_id is the PK of that table. That will help. </p> <p>You get the idea, I hope. Indexes help not only with WHERE clauses but with ON clauses.</p> <p>By the way, the full-text index mentioned in another answer won't help with this JOIN performance problem you are having. </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