Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This SQL has many things that are redundant that may not show up in an <code>explain</code>.</p> <p>If you require a field, it shouldn't be in a table that's in a LEFT JOIN - left join is for when data might be in the joined table, not when it has to be.</p> <p>If all the required fields are in the same table, it should be the in your first FROM. </p> <p>If your text search is predictable (not from user input) and relates to a single known ID, use the ID not the text search (props to Patricia for spotting the LIKE bottleneck).</p> <p>Your query is hard to read because of the lack of table hinting, but there does seem to be a pattern to your field names.</p> <p>You require <code>nMmbGndrID</code> and <code>nMmbAge</code> to have a value, but these are probably in MEMBERS, which is 5 left joins down. That's a redundancy.</p> <p>Remember that you can do a simple join like this:</p> <p><code>FROM sspk.villages, gps, TALUKS, dists, HHINFO, MEMBERS [...] WHERE [...] nVlgGpID = nGpID AND nGpTlkID = nTlkID AND nTlkDistID = nDistID AND nHLstGpID = nGpID AND nHLstID = nMmbHhiID</code></p> <p>It looks like <code>cSttsDesc</code> comes from <code>STATUS</code>. But if the text <code>'No, Eligible'</code> matches exactly one nBStsSttsID in <code>BNFTSTTS</code> then find out the value and use that! If it is 7, take out <code>LEFT JOIN STATUS ON nBStsSttsID = nSttsID</code> and replace <code>AND cSttsDesc like 'No, Eligible'</code> with <code>AND nBStsSttsID = '7'</code>. This would see a massive speed improvement.</p>
    singulars
    1. This table or related slice is empty.
    1. POOptimizing the SQL Query to reduce execution time
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPaul Gregory
    1. USPaul Gregory
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COFirst of All Thanks for the reply .
      singulars
      1. PO
      1. USAbhishek
    2. COFirst of All Thanks for the reply . The naming of table is correct as you have identify . The way you have given the solution is really executing fast but it is missing some records according to my test data . Also , the text 'No, Eligible' is a status for a benefits and it is not matching exactly one nBStsSttsID in BNFTSTTS so i had to use the more redundant method 'LIKE' To get teh result . However If possible i will try to normalize my table structure to reduce teh execution time further . Any way thanks for the solution and once solved i will post my original query here . Thank you
      singulars
      1. PO
      1. USAbhishek
    3. COWell then Patricia's idea of adding a boolean field to BNFTSTTS and setting it to true for each of the nBStsSttsIDs you need in this query will help you, as you can just `nBStsSttsIsLikeNoEligible = true`. You could even populate this using a query.
      singulars
      1. PO
      1. USPaul Gregory
 

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