Optimizing the SQL Query to reduce execution time

Question

My SQL Query with all the filters applied is returning 10 lakhs (one million) records . To get all the records it is taking 76.28 seconds .. which is not acceptable . How can I optimize my SQL Query which should take less time. The Query I am using is :

    SELECT cDistName , cTlkName, cGpName, cVlgName , 
           cMmbName , dSrvyOn 
      FROM sspk.villages 
 LEFT JOIN gps  ON nVlgGpID = nGpID
 LEFT JOIN TALUKS ON nGpTlkID = nTlkID   
 left JOIN dists ON nTlkDistID = nDistID
 LEFT JOIN HHINFO ON nHLstGpID = nGpID
 LEFT JOIN MEMBERS ON nHLstID = nMmbHhiID
 LEFT JOIN BNFTSTTS  ON nMmbID = nBStsMmbID
 LEFT JOIN STATUS ON nBStsSttsID = nSttsID
 LEFT JOIN  SCHEMES ON  nBStsSchID = nSchID
     WHERE (
               (nMmbGndrID = 1 and nMmbAge between 18 and 60) 
           or  (nMmbGndrID = 2 and nMmbAge between 18 and 55)
           )
      AND cSttsDesc like 'No, Eligible' 
      AND DATE_FORMAT(dSrvyOn , '%m-%Y') < DATE_FORMAT('2012-08-01' , '%m-%Y' )
 GROUP BY cDistName , cTlkName, cGpName, cVlgName , 
        DATE_FORMAT(dSrvyOn , '%m-%Y')

I have searched on the forum and outside and used some of the tips given but it hardly makes any difference . The joins that i have used in above query is left join all on Primary Key and Foreign key . Can any one suggest me how can I modify this sql to get less execution time ....

Answer 1

You are, sir, a very demanding user of MySQL! A million records retrieved from a massively joined result set at the speed you mentioned is 76 microseconds per record. Many would consider this to be acceptable performance. Keep in mind that your client software may be a limiting factor with a result set of that size: it has to consume the enormous result set and do something with it.

That being said, I see a couple of problems.

First, rewrite your query so every column name is qualified by a table name. You'll do this for yourself and the next person who maintains it. You can see at a glance what your WHERE criteria need to do.

Second, consider this search criterion. It requires TWO searches, because of the OR.

 WHERE (
           (MEMBERS.nMmbGndrID = 1 and MEMBERS.nMmbAge between 18 and 60) 
       or  (MEMBERS.nMmbGndrID = 2 and MEMBERS.nMmbAge between 18 and 55)
       )

I'm guessing that these criteria match most of your population -- females 18-60 and males 18-55 (a guess). Can you put the MEMBERS table first in your list of LEFT JOINs? Or can you put a derived column (MEMBERS.working_age = 1 or some such) in your table?

Also try a compound index on (nMmbGndrID,nMmbAge) on MEMBERS to speed this up. It may or may not work.

Third, consider this criterion.

  AND DATE_FORMAT(dSrvyOn , '%m-%Y') < DATE_FORMAT('2012-08-01' , '%m-%Y' )

You've applied a function to the dSrvyOn column. This defeats the use of an index for that search. Instead, try this.

  AND dSrvyOn >= '2102-08-01'
  AND dSrvyOn <  '2012-08-01' + INTERVAL 1 MONTH

This will, if you have an index on dSrvyOn, do a range search on that index. My remark also applies to the function in your ORDER BY clause.

Finally, as somebody else mentioned, don't use LIKE to search where = will do. And NEVER use column LIKE '%something%' if you want acceptable performance.

Answer 2

You claim yourself you base your joins on good and unique indexes. So there is little to be optimized. Maybe a few hints:

• try to optimize your table layout, maybe you can reduce the number of joins required. That probably brings more performance optimization than anything else.

• check your hardware (available memory and things) and the server configuration.

• use mysqls explain feature to find bottle necks.

• maybe you can create an auxilliary table especially for this query, which is filled by a background process. That way the query itself runs faster, since the work is done before the query in background. That usually works if the query retrieves data that must not neccessarily be synchronous with every single change in the database.

• check if an RDBMS is really the right type of database. For many purposes graph databases are much more efficient and offer better performance.

Answer 3

Try adding an index to nMmbGndrID, nMmbAge, and cSttsDesc and see if that helps your queries out.

Additionally you can use the "Explain" command before your select statement to give you some hints on what you might do better. See the MySQL Reference for more details on explain.

Answer 4

This SQL has many things that are redundant that may not show up in an explain.

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.

If all the required fields are in the same table, it should be the in your first FROM.

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).

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.

You require nMmbGndrID and nMmbAge to have a value, but these are probably in MEMBERS, which is 5 left joins down. That's a redundancy.

Remember that you can do a simple join like this:

FROM sspk.villages, gps, TALUKS, dists, HHINFO, MEMBERS [...] WHERE [...] nVlgGpID = nGpID AND nGpTlkID = nTlkID AND nTlkDistID = nDistID AND nHLstGpID = nGpID AND nHLstID = nMmbHhiID

It looks like cSttsDesc comes from STATUS. But if the text 'No, Eligible' matches exactly one nBStsSttsID in BNFTSTTS then find out the value and use that! If it is 7, take out LEFT JOIN STATUS ON nBStsSttsID = nSttsID and replace AND cSttsDesc like 'No, Eligible' with AND nBStsSttsID = '7'. This would see a massive speed improvement.

Answer 5

If the tables used in joins are least use for updates queries, then you can probably change the engine type from INNODB to MyISAM.

Select queries in MyISAM runs 2x faster then in INNODB, but the updates and insert queries are much slower in MyISAM.

Answer 6

You can create Views in order to avoid long queries and time.

Answer 7

Your like operator could be holding you up -- full-text search with like is not MySQL's strong point.

Consider setting a fulltext index on cSttsDesc (make sure it is a TEXT field first).

ALTER TABLE articles ADD FULLTEXT(cSttsDesc);

SELECT
    *
FROM
    table_name
 WHERE MATCH(cSttsDesc) AGAINST('No, Eligible')

Alternatively, you can set a boolean flag instead of cSttsDesc like 'No, Eligible'.

Source: http://devzone.zend.com/26/using-mysql-full-text-searching/