SQL fetching advice needed.

hgtonight

@businessdad said:
Regarding your question about profiling, you might want to have a look at the excellent XDebug.

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.

peregrine

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.

businessdad

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.

peregrine

Thank you @businessdad

summing up.

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).

R_J

More than one article and not always mysql, but a start: http://www.mysqlperformanceblog.com/

peregrine

@R_J said:
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

businessdad

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.

peregrine

@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

Aolee

@peregrine said:
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)

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.

peregrine

@aolee

thx for your insights

glad my question brought you out of the woodwork, haven't seen you comment in a while.

Aolee

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.

peregrine

@Aolee - I just checked. Thank you for the donation for the plugins. Much appreciated.

and thanks for the insights.