SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    which is more efficient: repeated queries or storing result in session?

    Hi,

    I want to pull a random picture out of my database, based on album access for the user who is logged in. (See a detailed description here)

    My first go was to try a solution which just needed one MySQL query, but appearantly, that's not possible. Instead, I've now got the following code:
    PHP Code:
    function getRandomImage($userId)
    {
        
    // Get all albums user has access to
        
    $db =& new DbConnection(DB_HOSTNAMEDB_USERNAMEDB_PASSWORDDB_DBNAME);
        
    $sql "SELECT " ALBUM_TABLE_NAME "." ALBUM_COLUMN_ID " FROM " ALBUM_TABLE_NAME ", " USER2ALBUM_TABLE_NAME " WHERE " ALBUM_TABLE_NAME "." ALBUM_COLUMN_USERID " = ? " " OR (" USER2ALBUM_TABLE_NAME "." USER2ALBUM_COLUMN_ALBUMID " = " ALBUM_TABLE_NAME "." ALBUM_COLUMN_ID " AND " USER2ALBUM_TABLE_NAME "." .USER2ALBUM_COLUMN_USERID " = ?)";    
        
    $query =& $db->createQuery($sql);
        
    $query->setQueryParameter('validateNumeric'$_SESSION['user']->session->getVariable('user_id'));
        
    $query->setQueryParameter('validateNumeric'$_SESSION['user']->session->getVariable('user_id'));
        
    $query->makePreparedQueryString();
        
    $result =& $query->executeQuery();
        while (
    $row $result->fetchRowAssoc())
        {
            
    $albums[] = $row[ALBUM_COLUMN_ID];
        }

        
    // Build an array with all pictures from all albums
        
    foreach ($albums as $album)
        {
            
    $sql "SELECT " PICTURE_COLUMN_ID ", " PICTURE_COLUMN_TITLE ", " PICTURE_COLUMN_EXTENSION " FROM " PICTURE_TABLE_NAME " WHERE " ALBUM_COLUMN_ID " = ?";
            
    $query->newQuery($sql);
            
    $query->setQueryParameter('validateNumeric'$album);
            
    $query->makePreparedQueryString();
            
    $result =& $query->executeQuery();
            while (
    $row $result->fetchRowAssoc())
            {
                
    $picture_id intval($row[PICTURE_COLUMN_ID]);
                if (!empty(
    $row[PICTURE_COLUMN_EXTENSION]))
                    
    $pictures[] = ALBUM_DIR_WWW $album "/" $picture_id $row[PICTURE_COLUMN_EXTENSION];
            }
        }
        
    $randomKey array_rand($pictures1);
        return 
    $pictures[$randomKey];

    My question is what's more effecient in terms of resources on my host; the solution above, where the queries are run every time getRandomImage() is called, or a solution were getRandomImage is called only if the picture array isn't stored in a session variable and then have
    PHP Code:
        $randomKey array_rand($pictures1);
        return 
    $pictures[$randomKey]; 
    as a function of it's own, operation on this session variable.

    So: What's better for my server: Fewer sql queries or less information stored as session variables?
    //Anders

  2. #2
    SitePoint Enthusiast oemberton's Avatar
    Join Date
    Oct 2004
    Location
    Derby, UK
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    I'd use the session variable if you're concerned about speed, but the SQL queries you've done could be a lot quicker.

    At the moment you query pretty much the whole database to pick out a single image. You could easily pull a random entry out directly via SQL, i.e.

    SELECT RAND() FROM ... LIMIT 1

    which will save you most of the DB time. Also you don't need to check every album - only one at random - and if that's empty try another.

    This said unless you get a stunning amount of traffic with PHP / MySQL performance here is not going to be an issue.

  3. #3
    SitePoint Zealot DraginX's Avatar
    Join Date
    Mar 2003
    Location
    US
    Posts
    161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One lil tip, try hard not to put queries in loops. Look up JOINS and multiple table selecting =)

  4. #4
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oemberton
    SELECT RAND() FROM ... LIMIT 1
    Won't work since that can result in a picture from an album the user hasn't access to. What I do with the first query is to get a list of all albums the user has access to, then I loop over these albums to get a list of all pictures in these albums. And finally, I randomize and get a single picture out of that array.
    //Anders

  5. #5
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DraginX
    One lil tip, try hard not to put queries in loops. Look up JOINS and multiple table selecting =)
    That was my first thought as well, but
    this reply says it isn't possible...
    //Anders

  6. #6
    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)
    oh, it's possible, but it's not at all efficient

    let's say you have an album that has 12 songs and 15 people have access to it

    if you did a join, it would return 180 rows

    now, you do want to choose something randomly out of that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    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)
    er, i mean pictures, not songs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot DraginX's Avatar
    Join Date
    Mar 2003
    Location
    US
    Posts
    161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh lol I just gave it aq quick glance, my mistake =) if you're concerned about access umm let's say you a field called 'accessLevel' and then the pic table has 'level' Now the account has accessLevel of 1 and so does the pic so...

    SELECT RAND() FROM pic WHERE level <= accounts.accessLevel

    Hope I explained it okay lol

  9. #9
    SitePoint Enthusiast oemberton's Avatar
    Join Date
    Oct 2004
    Location
    Derby, UK
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Won't work since that can result in a picture from an album the user hasn't access to. What I do with the first query is to get a list of all albums the user has access to, then I loop over these albums to get a list of all pictures in these albums. And finally, I randomize and get a single picture out of that array.
    Sure - but you can do the RAND() query for the second part? Hence two queries: one for albums they have rights for, the second would be along the lines of:

    SELECT RAND() WHERE condition1=true OR condition2=true OR ...

    I'm willing to bet there's a way to do it in one SQL statement but I don't know how!

  10. #10
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    assuming the following relationships:

    1 user has many galleryies, one gallery has many usersM:N)
    on gallery has many pictures, on picture has only one gallery.(M:1)

    therefore lets assume u have the folllowing tables:

    users
    PK = user_ID

    user_gals (linking table to facilitate M:N relationship btw users and gallery)
    PK = user_ID, gal_ID

    Galleries
    PK = gal_ID

    Pictures:
    PK = pic_ID
    FK = gal_ID

    //where PK = Primary key and FK = foreign key

    SQL would look something like this: (just using *'s for simplicity, dont think you would actually be able to use *, would have to explicitly name columns)



    Code:
    SQL: 
    
    select galleries.*,users.*,pictures.* 
    from users 
    JOIN user_gals ON users.user_ID = user_gals.user_Id
    JOIN galleries ON user_gals.gal_ID = galleries.gal_ID
    JOIN pictures ON galleries.gal_ID = pictures.gal_ID
    WHERE users.user_ID = 'theUsersName'
    GROUP BY picture.pic_ID
    ORDER BY RAND()
    limit 0,1
    I'm not sure this will actually work (couldnt test it), but it should get you all the user details, galleries, and picture information for a certain user in random order and will only return 1 result (limit 0,1).

    you can play around with the WHERE clause and GROUP BY, but this query *should* get you all the information you could possibly want to play with.

    You might also want to play around with INNER JOINS..

  11. #11
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    now, you do want to choose something randomly out of that?
    Yes? Should that be a problem?
    //Anders

  12. #12
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by toasti
    Code:
    select galleries.*,users.*,pictures.* 
    from users 
    JOIN user_gals ON users.user_ID = user_gals.user_Id
    JOIN galleries ON user_gals.gal_ID = galleries.gal_ID
    JOIN pictures ON galleries.gal_ID = pictures.gal_ID
    WHERE users.user_ID = 'theUsersName'
    GROUP BY picture.pic_ID
    ORDER BY RAND()
    limit 0,1
    Thanks, this looks at least as something for me to build on (have actually tested it yet).
    //Anders

  13. #13
    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)
    Quote Originally Posted by toasti
    You might also want to play around with INNER JOINS..
    those were inner joins!!



    Quote Originally Posted by thoresson
    Quote Originally Posted by r937
    now, you do want to choose something randomly out of that?
    Yes? Should that be a problem?
    other than performance, not really
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    those were inner joins!!
    oops. well i must have meant outer joins then!! like LEFT and RIGHT.

    my bad

  15. #15
    SitePoint Guru toasti's Avatar
    Join Date
    Feb 2004
    Location
    Grahamstown
    Posts
    634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ..but the SQL was right though hey? (r937)

    there might be performance overhead using the RAND() function (not too sure), you can avoid using SQL rand() by using php

    Code:
    $randNum = rand(0,$limit);
    
    $sql = "select ... limit $randNum,1"
    then you can drop the 'order by rand()'

    hope you winning

  16. #16
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    other than performance, not really
    What's causing the performace problem? The RAND() or the joins?
    //Anders

  17. #17
    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)
    the number of rows in the cross join

    200 users, 50 albums = 10000 rows that need to be sorted, just to pull a random one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    the number of rows in the cross join

    200 users, 50 albums = 10000 rows that need to be sorted, just to pull a random one
    So what would be a better way to do it? The way I do it now? First choose a random album and then choose a random picture from that album?
    //Anders

  19. #19
    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)
    well, i'm not going to guarantee that it's better, but that would be my suggestion (i know i did not actually come out and say so in that other thread, i guess i should have been more explicit)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict thoresson's Avatar
    Join Date
    Dec 2002
    Location
    Gothenburg, Sweden
    Posts
    255
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by DraginX
    One lil tip, try hard not to put queries in loops. Look up JOINS and multiple table selecting =)
    Because queries inside loops generates multiple queries, while joins just one?
    //Anders

  21. #21
    SitePoint Zealot DraginX's Avatar
    Join Date
    Mar 2003
    Location
    US
    Posts
    161
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by thoresson
    Because queries inside loops generates multiple queries, while joins just one?
    Yeah, although sometimes you HAVE to use queries in loops...unless you know like..all of MySQL's functions and whatnot lol. Even then you still might :S But like I said earlier, try hard to not do any query loops at all. Most of the case there is a work-around =)


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
  •