SitePoint Sponsor

User Tag List

Results 1 to 15 of 15

Thread: Slow query

  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,026
    Mentioned
    64 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,026
    Mentioned
    64 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,026
    Mentioned
    64 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
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Either I didn't index it properly (although it gave me a message it did) or it didn't sadly make any improvement to the speed of the query I did it using the above code, and also using the checkboxes you've mentioned in the table structure.

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,499
    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.

  9. #9
    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.

  10. #10
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,499
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by Michael Morris View Post
    ALTER TABLE phpvms_schedules ADD INDEX (code);

    I think.
    You think correctly

  11. #11
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have added an index to the code column, however it is still slow, any ideas? It is slow even if I take out the 'AND LOCATE (dayofweek...)' line or 'AND phpvms_schedules.enabled = '1'

  12. #12
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,026
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    hmm...

    Code sql:
    AND locate(
      dayofweek(
        convert_tz( now(), '+1:00', '+0:00')
      )-1, phpvms_schedules.daysofweek) > 0
    It's likely this. What is the datatype of that field, and what are you attempting to accomplish with this section of the query?

  13. #13
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Apologies for a late reply.

    I've removed it and the query was slow without it. Basically, what it does is it gets only the schedules from a table in which 'daysofweek' is current day. i.e today's a Monday. Monday is represented by 1 in 'daysofweek' column, hence it will only show schedules with 1 inside that column.

  14. #14
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,023
    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

  15. #15
    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
  •