Jump to content

How many is too many sql queries?


floridaflatlander

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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/

Link to comment
Share on other sites

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 :shrug::P

 

Thanks Muddy I was kind of wondering about that

 

I'll get the tables later today

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.