how many queries is too many?

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

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?

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

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

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