Web Analytics Made Easy -
StatCounter SQL query taking too long. - CodingForum

Announcement

Collapse
No announcement yet.

SQL query taking too long.

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL query taking too long.

    I am running a search query but it is taking too long (even when I use limits). I have tried cutting down on the number of joins as well but nothing seems to help. Normally the query takes longer than 3 minutes and the page times out.

    Any suggestions? I think it has something to do with the way I am joining the tables?

    PHP Code:
    $sql "SELECT t1.id_account, t1.mns_file_no, t1.contact_person, t1.company_name1, t2.username, t2.password, ";
    if (
    $countryon$sql .= "t3.country_name, ";
    $sql .= "t1.email, t1.date_last_changed, ";
    if (
    $pmapactiveon$sql .= "t4.id_pmaps, t4.period_active, t4.pmap_status, ";
    $sql .= "t2.user_active FROM tb_account AS t1 
    LEFT JOIN tb_user AS t2 ON t1.id_account = t2.id_account "
    ;
    if (
    $countryon$sql .= "LEFT JOIN tb_country AS t3 ON t1.id_country = t3.id_country ";
    if (
    $pmapactiveon$sql .= "LEFT JOIN tb_account_pmaps as t4 
    ON t1.id_account = t4.id_account "
    ;
    $sql .= "WHERE $where1 $where2 $where3 $where4 $where5 $like1 $like2 $like3 
    GROUP BY t1.id_account "
    ;
    $sql .= "ORDER BY $order_by $order_how $limit"

  • #2
    Ok, but what does the query look like? All you gave us is some PHP code that combines various strings together to form a SQL query, based on some conditionals. It would be nice to see the full query without any PHP fluff around it.

    Does the timeout happen with every possible query combination? Further, if you don't need to select empty values, an INNER JOIN instead of a LEFT JOIN could speed things up.

    And one last point: I've got no idea what WHERE conditions are used, but have you used an index on the columns you match against?
    De gustibus non est disputandum.

    Comment


    • #3
      The query changes depending on what filters the user selects... ie: he might type something for a username (as is the case below) but nothing for country, etc.

      The logic layer that builds the SQL (ie: that determines the $where variables) is far too long to add in this post. Besides, it works fine.

      Here is the output of an example filtering only on the username:

      PHP Code:
      SELECT t1.id_accountt1.mns_file_not1.contact_person
      t1.company_name1t2.usernamet2.password
      t3.country_namet1.emailt1.date_last_changedt2.user_active
      FROM tb_account 
      AS t1 LEFT JOIN tb_user AS t2 ON t1.id_account 
      t2.id_account LEFT JOIN tb_country AS t3 ON t1.id_country 
      t3.id_country WHERE username LIKE ucase('%mark%'GROUP BY 
      t1
      .id_account ORDER BY t1.id_account asc 
      Here is an example of searching on username and country and a particular product map:

      PHP Code:
      SELECT t1.id_accountt1.mns_file_not1.contact_person
      t1.company_name1t2.usernamet2.password
      t3.country_namet1.emailt1.date_last_changedt4.id_pmaps
      t4.period_activet4.pmap_statust2.user_active FROM 
      tb_account 
      AS t1 LEFT JOIN tb_user AS t2 ON t1.id_account 
      t2.id_account LEFT JOIN tb_country AS t3 ON t1.id_country 
      t3.id_country LEFT JOIN tb_account_pmaps as t4 ON 
      t1
      .id_account t4.id_account WHERE country_name 
      'Afghanistan' AND t4.id_pmaps like '1' AND username LIKE ucase('%mark%'GROUP BY t1.id_account ORDER BY t1.id_account asc 
      Yes the primary table (tb_account) is heavily indexed but I will try using inner joins. I wish I could provide a URL to the site, but unfort. is an administrative site for a subsidiary of the U.N.

      Comment


      • #4
        I figured it out. I was doing like searches on integer fields. Duh. I think inner joins helped too. What is the difference between inner, outer and left/right?

        Comment


        • #5
          Glad you figured it out.

          LIKE searches on integer fields slow down a query? I didn't know that. Are you sure? Regarding the difference between LEFT and RIGHT and OUTER and INNER JOINs, my explanation would be far too amateurish, but maybe these articles provide some insight for you:

          http://www.mysql.com/doc/en/JOIN.html
          http://www.devx.com/dbzone/Article/17403/0/page/1
          http://techrepublic.com.com/5100-6329-5142674.html
          De gustibus non est disputandum.

          Comment


          • #6
            Sorry, what I meant to say was that I was JOINING a country table and searching for the name of the country (country_name... VARCHAR) using LIKE instead of just searching the matching index (id_country... which is INTEGER... faster). Especially considering that the index is the foreign key in the account table.

            Thanks for those links.

            Comment

            Working...
            X