#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 1999
    Location
    Canada, Toronto
    Posts
    103
    Rep Power
    15
    Hi there!

    I'm building here a site in PHP3 with MySQL. I was just wondering, how many queries is too many? My first page now does 21 each time it's loaded.

    Of course, I understand it all depends on complicity of queries and other factors, but still, is there a general rule for this?

    Thank you in advance!


    ------------------
    Alex D. G.
    http://www.alexdg.com

  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    As few as possible

    There's no way to judge how many is optimal as too many factors weigh in. However, 21 does seem very excessive. Are you sure there isn't someway to optimize those with table joins?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 1999
    Location
    Canada, Toronto
    Posts
    103
    Rep Power
    15
    I dont have table joins... it's mostly regular SELECT with minimal WHERE options and no GROUP at all... a couple of INSERTs and that's it...

    6 UPDATE
    6 INSERT
    9 SELECT

    but i'm afraid i'll have to put more... i haven't implemented page counter yet...

    i think INSERTs are very fast since no search is required... as for the rest, i donno =(


    ------------------
    Alex D. G.
    http://www.alexdg.com

  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 1999
    Location
    London
    Posts
    110
    Rep Power
    0
    i had a look at your page and could only work out three possible select stmts, are you doing select stmts for each of the little images that change ?

    give us a clue as to what all the stmts are about and I'll have a look if you want

    t

    ------------------
    cheers
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 1999
    Location
    Canada, Toronto
    Posts
    103
    Rep Power
    15
    Neah... my page is a very earlier prototype of the system i'm working on now...

    here is the list of all mysql queries:

    --- retrieving all possible 468x60 banners
    SELECT * FROM ads_banners WHERE size='468x60' AND (impressions <= impressions_purchased OR impressions_purchased = 0) AND (clicks <= clicks_purchased OR clicks_purchased = 0) AND status='on' AND UNIX_TIMESTAMP(start) < '954345166' AND (UNIX_TIMESTAMP(end) >= '954345166' OR UNIX_TIMESTAMP(end) = '0')

    --- after chosing one randomly updating it's impressions number
    UPDATE ads_banners SET impressions=impressions+1 WHERE id='12'

    --- inserting banner id and host IP for clients statistic
    INSERT INTO ads_impressions (bannerid, host) VALUES ('12', '192.168.1.102')

    --- doing the same for a smaller banner 234x60
    SELECT * FROM ads_banners WHERE size='234x60' AND (impressions <= impressions_purchased OR impressions_purchased = 0) AND (clicks <= clicks_purchased OR clicks_purchased = 0) AND status='on' AND UNIX_TIMESTAMP(start) < '954345166' AND (UNIX_TIMESTAMP(end) >= '954345166' OR UNIX_TIMESTAMP(end) = '0')
    UPDATE ads_banners SET impressions=impressions+1 WHERE id='18'
    INSERT INTO ads_impressions (bannerid, host) VALUES ('18', '192.168.1.102')

    --- retrieving FOUR small buttons 88x31, updating impressions
    SELECT * FROM ads_banners WHERE size='88x31' AND (impressions <= impressions_purchased OR impressions_purchased = 0) AND (clicks <= clicks_purchased OR clicks_purchased = 0) AND status='on' AND UNIX_TIMESTAMP(start) < '954345166' AND (UNIX_TIMESTAMP(end) >= '954345166' OR UNIX_TIMESTAMP(end) = '0')
    UPDATE ads_banners SET impressions=impressions+1 WHERE id='7'
    INSERT INTO ads_impressions (bannerid, host) VALUES ('7', '192.168.1.102')
    UPDATE ads_banners SET impressions=impressions+1 WHERE id='5'
    INSERT INTO ads_impressions (bannerid, host) VALUES ('5', '192.168.1.102')
    UPDATE ads_banners SET impressions=impressions+1 WHERE id='9'
    INSERT INTO ads_impressions (bannerid, host) VALUES ('9', '192.168.1.102')
    UPDATE ads_banners SET impressions=impressions+1 WHERE id='4'
    INSERT INTO ads_impressions (bannerid, host) VALUES ('4', '192.168.1.102')


    --- following two queries pick one random message from one group of messages. First query makes sure that the group you are trying to get a message from is visible
    SELECT * FROM rnd_groups WHERE title='did you know'
    --- second is retrieving the actuall message
    SELECT * FROM rnd_random WHERE isshow='Y' AND groupid='2'

    --- getting four most recent tutorials
    SELECT * FROM tutorials ORDER BY time DESC LIMIT 0,4

    --- getting 10 most recent headlines
    SELECT *, DATE_FORMAT(time, 'M d h:i p') AS ptime FROM headlines ORDER BY time DESC LIMIT 0,10

    --- following two queries are for the voting poll. First one finds one active poll.
    SELECT * FROM poll_list WHERE status='1'
    --- second retrieves all possible answers for the poll
    SELECT * FROM poll_answers WHERE pollid='1' ORDER BY count DESC


    that's pretty much it.... i will have to add page statistic as well =((( i'm afraid it could go up to 30 queries per page after all components are in the place....

    Right now it takes 0.00031 sec for the main page to be generated by PHP3 on WIN2K PROF.


    Please, advice me if there is any way to reduce the amount of queries.



    ------------------
    Alex D. G.
    http://www.alexdg.com

Similar Threads

  1. Replies: 7
    Last Post: October 10th, 2003, 08:13 AM
  2. Normal amount of queries in forum script ?
    By Sponk in forum PHP Development
    Replies: 6
    Last Post: May 19th, 2003, 10:10 AM
  3. Simultaneous queries optimization
    By tilleul in forum MySQL Help
    Replies: 9
    Last Post: December 18th, 2001, 07:22 AM
  4. PHP and MYSQL queries
    By Pokeboy in forum PHP Development
    Replies: 3
    Last Post: December 5th, 2001, 12:23 PM
  5. Help with queries!
    By duckisme in forum Beginner Programming
    Replies: 1
    Last Post: August 2nd, 2001, 07:51 AM

IMN logo majestic logo threadwatch logo seochat tools logo