 | |
#11
 
| |||
| |||
|
Re: JOIN, or two separate queries? -
12-09-2011
, 06:39 AM
|
On Tuesday, December 6, 2011 10:51:12 PM UTC-5, Jerry Stuckle wrote: On 12/6/2011 10:16 PM, John Levine wrote: This is for a message board, where I have 2 tables: "subjects", and "posts". No, he's fetching one subject and all the posts related to that one subject. He just hasn't learned how to join tables. Depends what the id field is. Perhaps he could tell us. R's, John From his query and questions, I would say it is the thread id. If it weren't, his message board wouldn't be working now (assuming, of course, this is the actual SQL he's using, which is pretty likely, IMHO). You're correct in both counts; the `id` field is the thread ID, chosen by AI in the subjects, then assigned to posts to match. And, you are also correct that my intent with the second option was to get the one subject and all of the posts in one query. The concern I had was that doing so would create more fields in one array, which may make the processing side a tad slower. Meaning, the first option would have one array with 1 index with 5 fields, then a second array with an infinite number of indexes with 3 fields each; or, the second option would have one array with an infinite number of indexes, with 8 fields each (with all of the fields from the `subject` table being duplicated in each index). You're also correct that I've never really used JOIN, and never had a real reason to need it. If there's a correct way to use it that would inevitably create a faster, more efficient script, then I would greatly appreciate your advice. As I said before, this is more of a learning experience for me, so even if the increased performance is marginal, it's still worth my time to learn a more efficient method. |
In real life programming, there is "fast enough" and "not fast enough".
In the first case you move on. In the second case you find and fix
the problem. This is real life, not school.
What you are doing is premature optimization.
What is much more important than saving a few microseconds is coding
your programs for clarity and understandability. Relational databases
are called that for a reason - learn to use their features.
And stop worrying about non-problems.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#12
| |||
| |||
|
|
What you are doing is premature optimization. What is much more important than saving a few microseconds is coding your programs for clarity and understandability. Relational databases are called that for a reason - learn to use their features. And stop worrying about non-problems. |
Now that I write programs professionally, that does lead to the next point.Although this program is for my own site, when I build for clients, I don't want to have to go back in 6 months or a year and modify it for optimization. The client doesn't want to pay for me to fix my mistakes, obviously, and they really shouldn't have to.
But anyway, in this case, I'm definitely thinking long term. A few years ago, I was having a problem with too many queries at the same time bogging down the system (which was resolved with some Apache and MySQL configuration changes, and then later perfected with an upgrade in hardware), so I'm always keeping an eye out for ways to decrease the number of queries. While it's not a problem NOW, the site traffic is increasing exponentially, so there's no reason to assume that I won't have a problem in the near future.
And, for me, it's easier to work on future problems during my slow season, rather than wait for them to happen (potentially during my busy season) andTHEN drop everything to work on it.
So, I guess the real question is, am I correct in assuming that one query with a JOIN (even like this, where it's not really a join) is always better than two queries?
If so, then the next question isn't really with MySQL; it's just determine whether one large array in PHP is better than two smaller arrays.
#13
| ||||||
| ||||||
|
|
On Friday, December 9, 2011 7:39:41 AM UTC-5, Jerry Stuckle wrote: What you are doing is premature optimization. What is much more important than saving a few microseconds is coding your programs for clarity and understandability. Relational databases are called that for a reason - learn to use their features. And stop worrying about non-problems. I've read several times where you've advised against "premature optimization". I always find that to be a little funny, because the saying used to always be "do it right the first time". Back then, you would get fussed at if you had to keep going back and optimizing a program. |
does NOT mean spending hours or days fixing problems which don't exist.
Code readability and maintainability are most important. Speed is
either "fast enough" or "not fast enough". In the former case you move
on. In the latter case you find the problem and fix it.
Quote:
|
Now that I write programs professionally, that does lead to the next point. Although this program is for my own site, when I build for clients, I don't want to have to go back in 6 months or a year and modify it for optimization. The client doesn't want to pay for me to fix my mistakes, obviously, and they really shouldn't have to. |
optimizations.
Quote:
|
But anyway, in this case, I'm definitely thinking long term. A few years ago, I was having a problem with too many queries at the same time bogging down the system (which was resolved with some Apache and MySQL configuration changes, and then later perfected with an upgrade in hardware), so I'm always keeping an eye out for ways to decrease the number of queries. While it's not a problem NOW, the site traffic is increasing exponentially, so there's no reason to assume that I won't have a problem in the near future. |
exponentially. There is also no reason to believe that even if it does
there will be a problem.
If you're that worried about performance, write everything in assembler.
Quote:
|
And, for me, it's easier to work on future problems during my slow season, rather than wait for them to happen (potentially during my busy season) and THEN drop everything to work on it. |
they do, they will not be caused by the difference between one or two
queries.
Quote:
|
So, I guess the real question is, am I correct in assuming that one query with a JOIN (even like this, where it's not really a join) is always better than two queries? |
months down the road that answer may change as your database contents
change.
There is no absolute answer to your question.
Quote:
|
If so, then the next question isn't really with MySQL; it's just determine whether one large array in PHP is better than two smaller arrays. |
It's like asking "which programming language is better". It all depends
on the situation.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
#14
| |||
| |||
|
|
You're also correct that I've never really used JOIN, and never had a real reason to need it. If there's a correct way to use it that would inevitably create a faster, more efficient script, then I would greatly appreciate your advice. |
using joins. After all the joins *"are"* the relations..., well, not
exactly, but e.g.
inner join – get all related records for both tables
left/right join – get *all* records from one of the two tables (left or
right) and only the related from the other
Quote:
|
As I said before, this is more of a learning experience for me, so even if the increased performance is marginal, it's still worth my time to learn a more efficient method. |
when dealing with databases. :-)
--
mvh
PerL krøllalfa lyngsdalen prekk net
Registert GNU/Linux bruker #527575
#15
| |||
| |||
|
|
Out of curiosity: Why are you using a relational database if you're no using joins. After all the joins *"are"* the relations..., well, not exactly, but e.g. inner join – get all related records for both tables left/right join – get *all* records from one of the two tables (left or right) and only the related from the other |
MySQL was the easiest answer at the time: it was available, and I had a very small amount of experience with it (emphasis on "very"), so it fit the bill.
I've never really needed to use JOIN, and even now, I'm not sure if it has any value for my purposes. Jerry seems to think that I shouldn't consider it unless I'm having a problem. I don't know that I agree with the logic, but I'm not really having any problems, and Jerry has never led me astray, so....
FWIW, I do understand the logic behind using JOIN. I'm just not sure how using it would add any value to my database structure.
#16
| |||
| |||
|
|
On Saturday, December 10, 2011 8:42:15 AM UTC-5, Per Larsen wrote: Out of curiosity: Why are you using a relational database if you're no using joins. After all the joins *"are"* the relations..., well, not exactly, but e.g. inner join – get all related records for both tables left/right join – get *all* records from one of the two tables (left or right) and only the related from the other Trying to make a long story short, I never really needed MySQL until a few years ago, when my low-traffic hobby site took off and became a high traffic site that required the majority of my time. Until then, most of the site was just using a series of flat text files, but I needed something faster and easier to maintain. MySQL was the easiest answer at the time: it was available, and I had a very small amount of experience with it (emphasis on "very"), so it fit the bill. I've never really needed to use JOIN, and even now, I'm not sure if it has any value for my purposes. Jerry seems to think that I shouldn't consider it unless I'm having a problem. I don't know that I agree with the logic, but I'm not really having any problems, and Jerry has never led me astray, so... |
PERFORMANCE UNLESS YOU ARE HAVING A PROBLEM. THEY ARE TWO ENTIRELY
DIFFERENT THINGS!
In fact, I said you SHOULD be using the relational features of a
relational database.
Quote:
|
FWIW, I do understand the logic behind using JOIN. I'm just not sure how using it would add any value to my database structure. |
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.