SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: slow load

  1. #1
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    slow load

    Hi guys

    I have a query (posted below) it contains 12,000 records my problem is it takes about 30 seconds to load the page, is there a way i can fix this?

    Im only showing 10 rows on the page


    PHP Code:
      <? $q5=mysql_query("SELECT * FROM testhandsets INNER JOIN testdeals  ON 
       testhandsets.Handset_ID = testdeals.HandsetID 
      LEFT JOIN testtariffs ON 
      testtariffs.TariffID = testdeals.TariffID"
    );

  2. #2
    Avid Logophile silver trophy
    ParkinT's Avatar
    Join Date
    May 2006
    Location
    Central Florida
    Posts
    2,345
    Mentioned
    192 Post(s)
    Tagged
    5 Thread(s)
    use "Limit 10" and pull in a page at a time.
    Hopefully one of the resident MySQL gurus will give you more specifics.
    Don't be yourself. Be someone a little nicer. -Mignon McLaughlin, journalist and author (1913-1983)


    Git is for EVERYONE
    Literally, the best app for readers.
    Make Your P@ssw0rd Secure
    Leveraging SubDomains

  3. #3
    SitePoint Guru
    Join Date
    Oct 2004
    Location
    uk
    Posts
    853
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry i took that part out of my posted code, that is already there but its still very slow

  4. #4
    hi galen's Avatar
    Join Date
    Jan 2006
    Location
    New Haven, CT
    Posts
    1,228
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I also had a query that was taking forever to load. My solution was to use output buffering to save the content generated by the database into a file. Everytime the page was loaded it would check if the database had changed and if not, just load the static html file.

  5. #5
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    528
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    When you're pulling lots of data from the database and the page is loading slow I find it useful to check if its the query or the page load which is running slow.
    If its still slow with the LIMIT 10 (ie not much data being sent to the page) then your query is probably running slow. Although its a fairly simple query it could run slow if there's a lot of data in the tables and they are not properly indexed. What indexes do you have on the tables?
    Last edited by hessodreamy; Jan 7, 2008 at 08:05. Reason: edited for clarification

  6. #6
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by galen View Post
    I also had a query that was taking forever to load. My solution was to use output buffering to save the content generated by the database into a file. Everytime the page was loaded it would check if the database had changed and if not, just load the static html file.
    What method did you use for checking for changes in the database?

  7. #7
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    A query like that shouldn't run slow - are you sure it's that one query?

    try it in PHPMyAdmin, if it doesn't run slowly then it's going to be another part of your code, or another query.

    I'm not a big fan of joins, so I just use the typical join - maybe my method would be faster?:
    PHP Code:
    $q5 mysql_query("SELECT * FROM testhandsets, testdeals, testtarrifs WHERE testhandsets.Handset_ID = testdeals.HandsetID AND testtariffs.TariffID = testdeals.TariffID"); 
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  8. #8
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That would be a full cartesian join, and would definitely be slower. Always use a join instead of selecting from multiple tables, if you use the correct join type it will be much faster.

    If you select from multiple tables, what will happen is every row will be put in the result set against every row from the other table, and THEN filtered down via the where clause. If you use the correct join type in the first place, only the rows that match the conditions will be joined together in the first place.

  9. #9
    SitePoint Zealot
    Join Date
    Mar 2007
    Posts
    196
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something else that may help it a little bit is if you do not need the all of the data in each row change the * to the names of the colums you do need such as:
    PHP Code:
    SELECT Handset_ID FROM ... 
    Kayzio - We don't hesitate, we accelerate.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    good point, kayzio

    stormrider, your explanation of arkinstall's query hardly ever happens -- most database optimizers, including mysql, will incorporate the WHERE conditions into the data access algorithm (execution plan) and not retrieve the full cartesian product but only those rows that match according to those conditions

    but arkinstall, your query changes a left outer join to an inner join and will return fewer results

    and stormrider is quite right, writing JOIN syntax is much preferred
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    528
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Betcha its the indexing.
    Quote Originally Posted by galen View Post
    I also had a query that was taking forever to load. My solution was to use output buffering to save the content generated by the database into a file. Everytime the page was loaded it would check if the database had changed and if not, just load the static html file.
    Galen, how did you do this? I've not really looked at output buffering much.


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
  •