How many is too many sql queries?

[floridaflatlander]

Campfire type talk here.

 

I was reading about keeping your queries to a minimum and it got me to thinking about how a file.php that I have has six queries

 

1. I have a query to see if a person is a member, if so provide a link

 

2. A sql query that makes a list of states to select

 

3. A sql query that makes a list of categories to select

 

4. A sql query to display a table with selected info from above

 

5. A sql query for titles

 

6. A sql query for photo links that are in another db.

 

7. An my right column is blank but will probably have queries in the future.

 

Just wondering

 

Thanks

S

[drisate]

I think that it's a good habit to try to make as less queryes as possible because it does use server ressources ans slows down the website opening ... But ne thing you really have to avoid at all cost is having to close a MySQL connexion to open an other one to then come back to the first one. Playing like that from one DB to and Other is really the worst thing ever for a server.

[KevinM1]

Can you show your particular queries?  Chances are, you can combine some of them.

[malinens]

6 queries (if they are optimized) is not much. Sometimes it's better to implement caching mechanism which can lower mysql load many times...

[mar.and65]

Campfire type talk here.

 

I was reading about keeping your queries to a minimum and it got me to thinking about how a file.php that I have has six queries

 

1. I have a query to see if a person is a member, if so provide a link

 

2. A sql query that makes a list of states to select

 

3. A sql query that makes a list of categories to select

 

4. A sql query to display a table with selected info from above

 

5. A sql query for titles

 

6. A sql query for photo links that are in another db.

 

7. An my right column is blank but will probably have queries in the future.

 

Just wondering

 

Thanks

S

 

It's only 6 queries, I don't think it's too much ... would you post the quries here, I also would like to see if there any change of simplifying.

[kickstart]

Hi

 

6 isn't much.

 

Big problem comes when you have one query, loop around the results and within that loop you have another query. The number of queries can rapidly be massive.

 

All the best

 

Keith

[fenway]

And even so, 1 slow query is worse than 100 instant ones.

[Muddy_Funster]

Too many queries is when you are using more than you need to - it varies hugely dependant on circumstance and as such can't really be answered. 

 

Rather than worry about the amount of queries, focus on the logic of them - make each querie as efficient and effective as you can, and then the number of them will be of negligable difference anyway.

 

It's all about quality not quantity.

[floridaflatlander]

The results of these queries are on my local server

I use Simple Machine and SMF's ssi functions to allow a member to be in a certain group

These queries make a table of members in this group that have a profile that has more detail than the smf user profile

2 database connections are used, one for this stuff and one for the coppermine below. They are closed after the script runs

*/

// Query 1

// If member  has a profile provide a link to it

$q = "SELECT vendors.id_member, smf_members.id_member, id_group, additional_groups FROM vendors, smf_members

WHERE vendors.id_member = smf_members.id_member

AND (smf_members.id_group = '$allowed_cats' OR find_in_set('$allowed_cats', smf_members.additional_groups))

AND vendors.id_member = '$id_member'

AND vendors.display_profile = '1'"; //  display_profile if 1, if someone is bad and it's changed from 1 the profile wont display

 

//2

//Comes in with an include file for titles - gets the group from a get (the same get is used for the (isset($group) below) and uses $state to make the following titles

// Makes a h1 title - Florida Fishing Guides, Florida Canoeing Outfitters etc.

// This is in the header - the below query is in the middle column but I still may be able to combine them.

$q = "SELECT group, grouptitle  FROM ven_cats";

 

//3

//To select a category using <option> - ie canoeing outfitters, fishing guide etc.

$q = "SELECT group, grouptitle FROM ven_cats ORDER BY vcat ASC, group ASC";

 

//4

//To select a location using <option>, Florida, Georgia etc.

$q = "SELECT * FROM loca WHERE country='USA' AND loca > 1 ORDER BY state ASC";

 

//5

//If there is group & state selected use this query if not I use another query

// This gets info from four databases, and display it in a table. Vendors which holds the special profiles, loca which holds the states, smf_members which holds the allowed category '$allowed_cats' and links which is used for links between all the databases.

// links has the column's id, id_member, category, loca all positive integers and is used to in lieu of a flat file because some members will be in more than one category,

//An example would be one person being a Florida Freshwater Fishing Guide, Florida Saltwater Fishing Guide and Florida Canoe Outfitter

if ((isset($group)) && (isset($state))) {

$qg = "SELECT * FROM vendors, loca, smf_members, ven_cats, ven_links

WHERE (smf_members.id_group = '$allowed_cats' OR find_in_set('$allowed_cats', smf_members.additional_groups))

AND vendors.id_member = smf_members.id_member

AND vendors.id_member = ven_links.id_member

AND ven_links.cat = ven_cats.cat

AND ven_cats.cat = '$group'

AND ven_links.loca = loca.loca

AND loca.state = '$state'

AND vendors.display_profile = '1'

GROUP BY ven_links.id_member

ORDER BY name ASC LIMIT $start, $display";

}

 

6.

// This comes from a coppermine db, it is the second db connection. You can see a sample of what it would look like here

$q = "SELECT pid, aid, filepath, filename, title, owner_id FROM $fishing_photo_table ORDER BY pid DESC LIMIT $display";

 

You can see a sample of what the coppermine connection looks like here http://www.outdoorupdate.com/Fishing/

[Muddy_Funster]

Y'all need to use the code/php tags when you're posting code.

 

Without the actual tables there isn't much comment to make on the SQL code...and there is a distinct lack of a question

[floridaflatlander]

Muddy_F the reply was in response to this

 

Can you show your particular queries?  Chances are, you can combine some of them.

And this

... would you post the quries here, I also would like to see if there any change of simplifying.

 

Without the actual tables there isn't much comment to make on the SQL code...and there is a distinct lack of a question

 

Thanks Muddy I was kind of wondering about that

 

I'll get the tables later today

[Muddy_Funster]

no worries, I just thought we had driffted passed those parts of the conversation.