either item1 or item2 or item3 or item4 or ..... or item20.
these items are all a value in one column of the database.
e.g. col 2 is identified as a primary key
A )
is it better to do a prepare on select * from mytable where col2 = $itemnumber
loop through the 20 $itemnumbers
exec the query
get result and process
end the loop
or
B )
prepare statement (with 20 where)
select * from mytable where col2 = 'item1' or where col2 = item2 or where col2 = item3 ... or where col2 = item19 or where col2 = item20
collect result of 20 values.
I don't really understand the mechanics in the processing
A ) would result in 20 executes with one prepare statement
B ) Would have 20 where conditionals (at what point does the number multiple wheres have any impact and would 20 wheres have any impact)
second question
I have two tables
I am searching for xyz
if it is Table2 I'm fine if not I need to check Table1 to find a match and then query Table2 fo the final data.
where conditions (lettercol = xxx letter col = xyz and potentially lettercol = deg )
A ) Is it better to always do a join of the two table with my where conditions and get results.
B ) Or check table 2 with where conditions, if not there check table1 with where conditions, check table2 with where conditions
many time the result will be in table 2 but occassionally table1 needs to be checked to get an alternate match in table2
Forgive me for the silly questions. Hope I made them clear enough.
thanks much.
Also if you know of a site that explains the mechanics of a sql lookup and what is going on behind the scenes, I would also be appreciative.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
If one doesn't have experience with SQL, those are not silly questions.
1. Multiple queries, or multiple OR clauses?
Running multiple queries or a single query with multiple OR clauses won't make much of a difference. An OR clause, essentially, means Take everything that matches X, then take everything that matches Y. The way such queries are processed varies, depending on the RDBMS, but, more or less, they are equivalent, therefore you can choose the one you prefer.
2. Multiple queries, or a JOIN?
This depends on the distribution of the data. I would see two possible cases:
If data found in Table 1 is always also in Table 2, then I would go for a JOIN. This will simplify maintenance, because the query will always be the same for all cases.
If data can be found in either Table 1, or Table 2, or both, then I would go for two queries. This covers the case in which data cannot be retrieved starting from Table 1.
3. Some training material
A friend of mine, also a beginner with SQL, started with this: Writing SQL Queries: Let's Start with the Basics. It's written for SQL Server, but the principles apply to every RDBMS. What goes on behind the scenes, from a logical perspective, doesn't change much from one RDBMS to the other, while the actual implementation may vary greatly. I would suggest starting with the basics and take it from there.
Running multiple queries or a single query with multiple OR clauses won't make much of a difference.
so even 20 ors make not much difference thanks.
If data found in Table 1 is always also in Table 2, then I would go for a JOIN. This will simplify maintenance, because the query will always be the same for all cases.
a join it is then based on my data
data might not be in table 1 or table2 (but this rare).
I understand how to write queries syntactically - but I don't understand performance issues or optimization of queries, thanks for the article - will take a look
edit: I'm looking for a performance optimization article and what goes on in the innards (what is really going on by the database what is read into memory during a query, how long the data could be cached etc) rather than a syntax article. Is there something that the database does if you execute two identical queries that might be cached rather than the search through the table again behind the scenes in mysql).
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
funny you should mention that I did a google search on optimizing sql queries and found it
as well as the optimize section in the mysql documentation. Lets of experimenting and playing around to do. I seem to work on one thing that takes me to a tangent on something else to something else - which take me far adrift from my initial plan.
I don't want to sound like teaching you how to learn, but, if your objective is to get proficient with SQL, I would recommend to avoid reading about performance tuning and optimisation of one or the other RDBMS. That's stuff for DBAs, probably it will not be your concern any time soon.
Thanks for the advice, and thanks for the help. I appreciate it. That said:
My objective is to read and possibly pick up info about performance tuning and optimisation of DB (which may be esoterica that applies to DB Admins) as well as query optimization.
I know the basics of writing PDO sql statements, and I am still looking for info on how the Database Engine works based on types of queries. So, my goals are different than what you expect.
The way I work to learn is different then some, but thats how I get a deeper understanding of how things work.
I know what I know , but I also have an advantage that I know what I don't know (to a certain extent), that is why I am not offering to be a DB administrator, nor am I looking to be one, but at the same time I like to learn about db administration and performance tuning as well, some people don't some people do
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
either item1 or item2 or item3 or item4 or ..... or item20.
these items are all a value in one column of the database.
e.g. col 2 is identified as a primary key
A )
is it better to do a prepare on select * from mytable where col2 = $itemnumber
loop through the 20 $itemnumbers
exec the query
get result and process
end the loop
or
B )
prepare statement (with 20 where)
select * from mytable where col2 = 'item1' or where col2 = item2 or where col2 = item3 ... or where col2 = item19 or where col2 = item20
collect result of 20 values.
I don't really understand the mechanics in the processing
A ) would result in 20 executes with one prepare statement
B ) Would have 20 where conditionals (at what point does the number multiple wheres have any impact and would 20 wheres have any impact)
The way i do is, i always off load the processing to DB, incase there will be a perfomance hit, I troubleshoot the query (especially if there are a lot of joins), then if can't be fine tuned, i use the application level which is what you mentioned loop "through the 20 $itemnumbers"
second question
I have two tables
I am searching for xyz
if it is Table2 I'm fine if not I need to check Table1 to find a match and then query Table2 fo the final data.
where conditions (lettercol = xxx letter col = xyz and potentially lettercol = deg )
A ) Is it better to always do a join of the two table with my where conditions and get results.
B ) Or check table 2 with where conditions, if not there check table1 with where conditions, check table2 with where conditions
many time the result will be in table 2 but occassionally table1 needs to be checked to get an alternate match in table2
Forgive me for the silly questions. Hope I made them clear enough.
businessdad or anyone.
thanks much.
Also if you know of a site that explains the mechanics of a sql lookup and what is going on behind the scenes, I would also be appreciative.
for your second question, case to case basis, there's no really concrete rule i would say (i might be wrong). like for my experience dealing with millions of records, i find it weird sometimes that where (sub select) is faster than the 2 level table join (it varies on how many tables you are joining). I'm not a pro DBA so i try to do different query combination, or like trial and error to achieve the best query performance. try using EXPLAIN will help you a lot on troubleshooting sql query performance.
hehe too busy at work, ever since i transferred to a non-for profit org dealing with Large MIS. i also haven't had the chance to updated my forum for very long. by the way good job on the Badges and Reactions. I've just downloaded and did a few enhancement mod already. hope you got my small token. by the way, another thing i do for optimization is... try to use cache if possible. i use file caching (not bad also if you are on SSD) as i also have Limited RAM so memcached is not an option to me.
Comments
I was going to post this as it has been a tremendous help to me. Businessdad strikes again!
I will have to check out kcachegrind now.
Search first
Check out the Documentation! We are always looking for new content and pull requests.
Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.
Another perhaps silly question
I have 20 items that need to be found
either item1 or item2 or item3 or item4 or ..... or item20.
these items are all a value in one column of the database.
e.g. col 2 is identified as a primary key
A )
is it better to do a prepare on select * from mytable where col2 = $itemnumber
loop through the 20 $itemnumbers
exec the query
get result and process
end the loop
or
B )
prepare statement (with 20 where)
select * from mytable where col2 = 'item1' or where col2 = item2 or where col2 = item3 ... or where col2 = item19 or where col2 = item20
collect result of 20 values.
I don't really understand the mechanics in the processing
A ) would result in 20 executes with one prepare statement
B ) Would have 20 where conditionals (at what point does the number multiple wheres have any impact and would 20 wheres have any impact)
second question
I have two tables
I am searching for xyz
if it is Table2 I'm fine if not I need to check Table1 to find a match and then query Table2 fo the final data.
where conditions (lettercol = xxx letter col = xyz and potentially lettercol = deg )
A ) Is it better to always do a join of the two table with my where conditions and get results.
B ) Or check table 2 with where conditions, if not there check table1 with where conditions, check table2 with where conditions
many time the result will be in table 2 but occassionally table1 needs to be checked to get an alternate match in table2
Forgive me for the silly questions. Hope I made them clear enough.
@businessdad or anyone.
thanks much.
Also if you know of a site that explains the mechanics of a sql lookup and what is going on behind the scenes, I would also be appreciative.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
If one doesn't have experience with SQL, those are not silly questions.
1. Multiple queries, or multiple OR clauses?
Running multiple queries or a single query with multiple
ORclauses won't make much of a difference. AnORclause, essentially, means Take everything that matches X, then take everything that matches Y. The way such queries are processed varies, depending on the RDBMS, but, more or less, they are equivalent, therefore you can choose the one you prefer.2. Multiple queries, or a JOIN?
This depends on the distribution of the data. I would see two possible cases:
3. Some training material
A friend of mine, also a beginner with SQL, started with this: Writing SQL Queries: Let's Start with the Basics. It's written for SQL Server, but the principles apply to every RDBMS. What goes on behind the scenes, from a logical perspective, doesn't change much from one RDBMS to the other, while the actual implementation may vary greatly. I would suggest starting with the basics and take it from there.
My shop | About Me
Thank you @businessdad
summing up.
so even 20 ors make not much difference thanks.
a join it is then based on my data
data might not be in table 1 or table2 (but this rare).
I understand how to write queries syntactically - but I don't understand performance issues or optimization of queries, thanks for the article - will take a look
edit: I'm looking for a performance optimization article and what goes on in the innards (what is really going on by the database what is read into memory during a query, how long the data could be cached etc) rather than a syntax article. Is there something that the database does if you execute two identical queries that might be cached rather than the search through the table again behind the scenes in mysql).
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
More than one article and not always mysql, but a start: http://www.mysqlperformanceblog.com/
thx rj
funny you should mention that I did a google search on optimizing sql queries and found it
as well as the optimize section in the mysql documentation. Lets of experimenting and playing around to do. I seem to work on one thing that takes me to a tangent on something else to something else - which take me far adrift from my initial plan.
e.g.
http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html
http://www.askapache.com/mysql/performance-tuning-mysql.html
http://www.mysqlperformanceblog.com/tools/
https://my.hostmonster.com/cgi/help/435
http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/
http://www.techrepublic.com/article/three-easy-ways-to-optimize-your-mysql-queries/
http://20bits.com/article/10-tips-for-optimizing-mysql-queries-that-dont-suck
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
I don't want to sound like teaching you how to learn, but, if your objective is to get proficient with SQL, I would recommend to avoid reading about performance tuning and optimisation of one or the other RDBMS. That's stuff for DBAs, probably it will not be your concern any time soon.
My shop | About Me
@businessdad
Thanks for the advice, and thanks for the help. I appreciate it. That said:
My objective is to read and possibly pick up info about performance tuning and optimisation of DB (which may be esoterica that applies to DB Admins) as well as query optimization.
I know the basics of writing PDO sql statements, and I am still looking for info on how the Database Engine works based on types of queries. So, my goals are different than what you expect.
The way I work to learn is different then some, but thats how I get a deeper understanding of how things work.
I know what I know
, but I also have an advantage that I know what I don't know (to a certain extent), that is why I am not offering to be a DB administrator, nor am I looking to be one, but at the same time I like to learn about db administration and performance tuning as well, some people don't some people do 
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
The way i do is, i always off load the processing to DB, incase there will be a perfomance hit, I troubleshoot the query (especially if there are a lot of joins), then if can't be fine tuned, i use the application level which is what you mentioned loop "through the 20 $itemnumbers"
for your second question, case to case basis, there's no really concrete rule i would say (i might be wrong). like for my experience dealing with millions of records, i find it weird sometimes that where (sub select) is faster than the 2 level table join (it varies on how many tables you are joining). I'm not a pro DBA so i try to do different query combination, or like trial and error to achieve the best query performance. try using EXPLAIN will help you a lot on troubleshooting sql query performance.
@aolee
thx for your insights
glad my question brought you out of the woodwork, haven't seen you comment in a while.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.
hehe too busy at work, ever since i transferred to a non-for profit org dealing with Large MIS. i also haven't had the chance to updated my forum for very long. by the way good job on the Badges and Reactions. I've just downloaded and did a few enhancement mod already. hope you got my small token. by the way, another thing i do for optimization is... try to use cache if possible. i use file caching (not bad also if you are on SSD) as i also have Limited RAM so memcached is not an option to me.
@Aolee - I just checked. Thank you for the donation for the plugins. Much appreciated.
and thanks for the insights.
I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.