SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 32 of 32
  1. #26
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Removed the RAND section all together for now and it's sped up the search by about half which is good but still a bit slow.

    Not been able to use JOIN yet - should that see a speed increase?

  2. #27
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    I would need a database person's expertise to back me up, but to my head it SHOULD do this;

    I do an implicit join (SELECT ... FROM tablea,tableb,tablec...) I then have to inner join EVERY row of table A and table B. I then inner join that combined table with table C.
    Then i take the entire thing, and THEN pair down the results based on the WHERE.

    If instead i explicitly define my join conditions (SELECT ... FROM tablea INNER JOIN tableb ON tablea.fileid = 10 AND tablea.bjoin = tableb.ajoin INNER JOIN tablec ON tablea.cjoin = tablec.ajoin ....)
    Now my first join only holds rows where fileid is 10. This should make the second (and subsequent) joins faster, because there are less records to join to.

    Now, i could be completely talking out of my rear, but thats how it works in my head
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #28
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Again, would I see significant speed improvements?

    The websites are on a shared server at the moment and we're thinking of switching to a dedicated server. My issue is that our other (original) website works fine and searches are almost instant (there are difference in database tables and therefore the way searches are carried out). The new site is really slow BUT they're both on the same server - so I don't know how much of a benefit moving to a dedicated server would provide.

  4. #29
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you please post the whole, entire "monster query" as it currently stands and the output of a SHOW CREATE TABLE for each of the tables that the query uses?

    What information is required to be output?

    Approximately how many records are there in the database you're testing it against?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #30
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Yeah... is it at all possible for you to even dump some of the tables complete with their data so we can do some testing?

    If the tables are massive (which they probably are if we're dealing with a query that takes a long time), or if the tables contain any sensitive data then scrap that idea and just give us the structure of the tables and an idea of how much data is contained in each table...

    Cheers

  6. #31
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by StarLion View Post
    I would need a database person's expertise to back me up, but to my head it SHOULD do this;

    I do an implicit join (SELECT ... FROM tablea,tableb,tablec...) I then have to inner join EVERY row of table A and table B. I then inner join that combined table with table C.
    Then i take the entire thing, and THEN pair down the results based on the WHERE.

    If instead i explicitly define my join conditions (SELECT ... FROM tablea INNER JOIN tableb ON tablea.fileid = 10 AND tablea.bjoin = tableb.ajoin INNER JOIN tablec ON tablea.cjoin = tablec.ajoin ....)
    Now my first join only holds rows where fileid is 10. This should make the second (and subsequent) joins faster, because there are less records to join to.

    Now, i could be completely talking out of my rear, but thats how it works in my head
    I had to look it up to ensure research hadn't come out with some different results, but I can't find any...

    There's no negligible performance difference between the two syntaxs. The only real difference between the two is the DBMS will convert the implicit join syntax to the explicit before executing. Otherwise it's exactly the same.

    The savings is on having someone look at your sql code - it's faster to establish the PK/FK combinations, PLUS it's easier to ensure the join actually occurs and you're not returning ALL rows from both tables (that happens a lot more than you would think....)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  7. #32
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Looking at your generated SQL, that subquery looks expensive. I have a feeling that might be what's causing you issues.

    Generating the resultset FIRST, then using that resultset as a JOINed table to group by and get the price might be a better approach. But I'd need data to see for sure.
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse


Tags for this Thread

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
  •