Page 1 of 2
Database SELECT Query performance
Posted: Thu Nov 04, 2004 1:21 pm
by kendall
DOnt know if this shud be put in here so im sorry but in designing my code for querying databases how does multiple querying affect the performance of the database and server relatively on average?
to give example of what i mean
if i
Code: Select all
SELECT from products, attributes LEFT JOIN on attributes WHERE...
after getting this data and outputting i would probably want to
Code: Select all
do{ SELECT from attributes WHERE productid.... }while(mysql_fetch_assoc());
usually i try to avoid querying the database when looping but sometimes its unaviodable isnt it? I mean lets suppose i have to do like more than 2 queries within a operation how does it affect database performance especially if i do querying amongst multiple tables?
Kendall
Posted: Thu Nov 04, 2004 2:23 pm
by Weirdan
Usually it's better to use one complex query to fetch all the data you need at once. One exception exists though: if you're using MyISAM tables it's better to make sure your queries are executed quick enough (MyISAM does not support page/row level locking, instead it locks entire table, thus effectively preventing any modifications to the table while SELECT query is executed).
Posted: Fri Nov 05, 2004 3:24 am
by swdev
I agree with Weirdan, using one complex query is the better way to go.
A couple of extra points
- make sure the joined fields are indexed, if possible.
- select just the fields you require, not the all of the fields, unless you really need them
- use WHERE clauses to restrict the number of records to just those that you actually want
Checkout this link for more on optimising queries [mysql_man]
http://dev.mysql.com/doc/mysql/en/Query_Speed.html[/mysql_man]
hope this helps.[/mysql_man]
Posted: Fri Nov 05, 2004 11:07 am
by kendall
thanks for your advice guys
Kendall
Posted: Fri Nov 05, 2004 11:25 am
by kendall
One thing though. I find it hard to impliment a single complex query in cases where i am searching for results that have a many to one relation and grouping is not a option.
to give e.g.
I have 2 tables products and features
obviously the features table would have many rows related to the one id in products in a case like this this is why i have to
do{ echo $productID SELECT FROM features WHERE ID = $ID}while($mysql_fetch_assoc());
are you guys telling me that there is a way arong this?
Kendall
Posted: Fri Nov 05, 2004 12:18 pm
by timvw
Posted: Fri Nov 05, 2004 12:22 pm
by xisle
Posted: Fri Nov 05, 2004 12:39 pm
by swdev
kendall
Show us the design of the tables in question, some sample data and the results that you want back.
Maybe then I can come up with a relevant query for you.
Posted: Fri Nov 05, 2004 1:04 pm
by kendall
OH MY GOD! SUBQUERIES! HAHAHA this could be good in the case of a DELETE query i am wokring on to delete related data from multiple tables
swdev,
here is an e.g.
products----
ID| NAME| DEscription
123 | product 1 | some text
321 | product 2 | another text
345 | product 3 | this text
features-----
ID | productId | feature
1 | 123 | one feature
2 | 123 | another feature
3 | 123 | 3rd feature
4 | 345 | good feature
5 | 213 | basic features
6 | 213 | ok enuff with this
now how i tried to do a complex query to retireve the productID name and features but it will have duplicated results and well i dont want DISTINCT results
so i query the products table then query the features table while looping true the results of the products table....to me this was the logical basic way? however i was just wondering if i had to do more than one querying during a loop of fecthing results how it would affect the server / database performance. how is a case like that handled.
I will dabble a bit with the sub queries. i dont think its what i need for this issue. but it will work great for a delete query i am working on
Kendall
Kendall
Posted: Fri Nov 05, 2004 7:09 pm
by swdev
Kendall
products----
ID| NAME| DEscription
123 | product 1 | some text
321 | product 2 | another text
345 | product 3 | this text
features-----
ID | productId | feature
1 | 123 | one feature
2 | 123 | another feature
3 | 123 | 3rd feature
4 | 345 | good feature
5 | 213 | basic features
6 | 213 | ok enuff with this
Given these tables, here is the SQL you need to retrieve the data
Code: Select all
sql = 'SELECT ' .
' products.id, products.name ' . // fields from products table
' , features.feature ' . // fields from features table
' FROM ' .
' products ' . // table names
' , features ' .
' WHERE ' .
' (products.id = features.productId) ' . // how the tables are joined
' ORDER BY ' . // odering clauses
' products.id ASC ' .
' , products.name ASC ';
This retrieves all the data from both tables, ordered by the Product Id, then the Product Name.
If you want to restrinct the data to a single product id, then just add another WHERE clasue to the existing WHERE clause.
If you want some other ordering, just change the ORDER BY clause
With this sample data, you won't get any records for product id 321 (no mathing records in the features table) and product id 213 (no matching records in the products table).
Hope this helps
Posted: Mon Nov 08, 2004 6:44 am
by kendall
Actually no it doesnt
by the way (213. forgot to put that in)
you see i need to list all the product along with all the features. Now correct me if i am wrong but wudnt your query bring up replicated data ie.
123 | product 1 | some text | one feature
123 | product 1 | some text | another feature
123 | product 1 | some text | one feature
123 | product 1 | some text | 3rd feature
and even if i were to do a DISTINCT query I wouldnt get the expected results
as far as i see i dont see any other solution working than to do multiple queries ...in this case atleast
Kendall
Posted: Mon Nov 08, 2004 9:14 am
by swdev
Kendall,
Given my original data and query, the results I get are
mysql> source 3.sql
+-----+-----------+------------------+
| id | name | feature |
+-----+-----------+------------------+
| 123 | product 1 | one feature |
| 123 | product 1 | another feature|
| 123 | product 1 | 3rd feature |
| 345 | product 3 | good feature |
+-----+-----------+------------------+
4 rows in set (0.18 sec)
Is this not what you require?
If it is not, please show me the output you require from the given data, and I'll see what I can come up with.
Posted: Mon Nov 08, 2004 9:48 am
by kendall
swdev,
the question is
One thing though. I find it hard to impliment a single complex query in cases where i am searching for results that have a many to one relation and grouping is not a option.
to give e.g.
I have 2 tables products and features
obviously the features table would have many rows related to the one id in products in a case like this this is why i have to
Quote:
do{ echo $productID SELECT FROM features WHERE ID = $ID}while($mysql_fetch_assoc());
are you guys telling me that there is a way arong this?
thus if i
i get
ID| NAME| DEscription
123 | product 1 | some text
321 | product 2 | another text
345 | product 3 | this text
However,
i needed to get the related information from the features table and the only way i knew how to do it wud be to
Code: Select all
do{
SELECT * from features WHERE product = product['ID']
}while(mysql_fetch_assoc())
Now the thing is i had a concern about the performance of my queries as i may have to do multiple select queries in the "do" loop. I have my tables indexed and i was advised to do more particular selects rather than the usuall "*" select.
then Wierdian said
usually it's better to use one complex query to fetch all the data you need at once.
and i replied
I find it hard to impliment a single complex query in cases where i am searching for results that have a many to one relation and grouping is not a option.
because using a complex query in this case to look up the 2 tables would yield duplicating results.
i.e.
if i
Code: Select all
SELECT * FROM products, features LEFT JOIN features ON products.ID = features.productID // assuming this is correct
i would get
| 123 | product 1 | one feature |
| 123 | product 1 | another feature|
| 123 | product 1 | 3rd feature |
| 345 | product 3 | good feature |
+-----+-----------+------------------+
NOT WAT I WANT
I needed to get
123|product 3|one feature|another feature|3rd feature
but i cant get that can i? thus re-iterating i had to get the id from the products first then use a "do" loop to get the features information
There was the implication that i could do a single query but i think i misread the comments because there isnt as far as i see. so my concern would more likely be performance of multiple select queries
Kendall
Posted: Mon Nov 08, 2004 10:12 am
by timvw
kendall wrote:
Code: Select all
SELECT * FROM products, features LEFT JOIN features ON products.ID = features.productID // assuming this is correct
i would get
| 123 | product 1 | one feature |
| 123 | product 1 | another feature|
| 123 | product 1 | 3rd feature |
| 345 | product 3 | good feature |
+-----+-----------+------------------+
NOT WAT I WANT
I needed to get
123|product 3|one feature|another feature|3rd feature
but i cant get that can i? thus re-iterating i had to get the id from the products first then use a "do" loop to get the features information
There was the implication that i could do a single query but i think i misread the comments because there isnt as far as i see. so my concern would more likely be performance of multiple select queries
Kendall
Actually you get what you need, 1 query. Now iterate over the results like
Code: Select all
$result = mysql_query("SELECT * FROM products, features LEFT JOIN features ON products.ID = features.productID");
// read first row from resultset
$row = mysql_fetch_assoc($result);
// initialise feature info
$info = '';
// initialise current product
$current_product = $row['product_name'];
// as long as there is a row
while ($row)
{
// this feature doesn't belong to the product we are processing anymore
if ($current_product != $row['product_name'])
{
// all the features for $current_product are in $info - use it
echo "PRODUCT: $current_product FEATURES: $info </br>";
// clear feature info
$info = '';
// move to next product
$current_product = $row['product_name'];
}
// add feature info
$info .= $row['feature'] . " | ";
// fetch next row
$row = mysql_fetch_assoc($result);
}
Posted: Mon Nov 08, 2004 10:34 am
by kendall
youre kidding me....is that really practical?? i mean its not just 2 tables but i can probably do 1 complex query in the do loop
hmmmm....
Kendall