SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Thread: Slow query

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Slow query

    I have written this query to extract information from a database to produce a foreach statement for rows in a table. It is however very slow, and it takes between 6-15 seconds to load the page.

    Code:
    $depquery = "SELECT * FROM phpvms_schedules 
    WHERE code = 'FE'
    AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,".TABLE_PREFIX."schedules.daysofweek)>0
    AND phpvms_schedules.enabled = '1'
    ORDER BY deptime ASC";
    
    $deplist = DB::get_results($depquery);
    Then I echo the table and elements inside it, and I use a foreach statement like this...

    Code:
    foreach($deplist as $flight)
    How can I speed this query up?

  2. #2
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Run an explain in PHPmyAdmin to get an idea of how the query is being put together and how long each step is taking.

    Code sql:
    EXPLAIN (
    SELECT * FROM phpvms_schedules 
    WHERE code = 'FE'
    AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,".TABLE_PREFIX."schedules.daysofweek)>0
    AND phpvms_schedules.enabled = '1'
    ORDER BY deptime ASC
    )

    Pay attention to what indexes it's choosing. If you have no indexes, that's likely a large source of the problem.

    Requesting thread move to the database forum.

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    I had to remove the following line because of an SQL syntax error:

    Code:
    AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,".TABLE_PREFIX."schedules.daysofweek)>0
    Attached Images Attached Images

  4. #4
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    Add an index on code, see if that helps

  5. #5
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies for a stupid question, but how do you add an index? Never did it before.

  6. #6
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    0 Thread(s)
    ALTER TABLE phpvms_schedules ADD INDEX (code);

    I think. There's a checkbox toggle for it on the table structure tab of the PHPmyAdmin interface.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by HighFlyerPL185 View Post
    I had to remove the following line because of an SQL syntax error:

    Code:
    AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,".TABLE_PREFIX."schedules.daysofweek)>0
    That's because when you run the query in PHPMyAdmin you have to manually substitute all PHP variables and constants in the query. In this case TABLE_PREFIX.
    If for example the value of TABLE_PREFIX is phpvms_, then that line would become:

    Code:
    AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,phpvms_schedules.daysofweek)>0
    So please run the EXPLAIN again on the entire query, let's see if it uses the index you have created.

  8. #8
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    That's because when you run the query in PHPMyAdmin you have to manually substitute all PHP variables and constants in the query. In this case TABLE_PREFIX.
    If for example the value of TABLE_PREFIX is phpvms_, then that line would become:

    Code:
    AND locate(dayofweek(convert_tz(now(),'+1:00','+0:00'))-1,phpvms_schedules.daysofweek)>0
    So please run the EXPLAIN again on the entire query, let's see if it uses the index you have created.
    Right. I understand now. I've ran EXPLAIN again, and it shows the exact same result as in the thumbnail in my post above.

  9. #9
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you please post the output of a SHOW CREATE TABLE for the table?

    What is the intended output of the query?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  10. #10
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    S8D0Mv0.jpg

    The output I intend the query to generate, is a list of upcoming flight departures for a virtual airline. The query is then ran by a foreach($deplist as $flight) statement which produces table rows, each being a single departure with all the information required.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •