With INSERT can I increment an existing value in a column?

On Wed, 16 Feb 2005 09:17:59 GMT, in mailing.database.mysql "Randell
D." wrote:

 >|
 >| Folks,
 >|
 >| I have a table of addresses and a seperate table with contact names -
 >| All addresses tie to one or more names - I would like to keep track of
 >| the number of names 'belonging' to an address and have thus included a
 >| column in my address table called num_of_contacts. Everytime I add a
 >| new contact, I would like to increment the num_of_contacts column in the
 >| address table.
 >|
 >| Is this possible?
 >|
 >| Me thinks not (or at least, my attempts so far have failed me) so I'd
 >| appreciate it if someone could tell me if I am wasteing my time trying
 >| and instead SELECT the record, increment it, then UPDATE it.

Its not a good idea storing calculated values into a table. Use
queries to return the most up-to-date information.

---------------------------------------------------------------
jnorthau.RemoveThis@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

Thomas Bartkus wrote:


 >
  >>Folks,
  >>
  >>I have a table of addresses and a seperate table with contact names -
  >>All addresses tie to one or more names - I would like to keep track of
  >>the number of names 'belonging' to an address and have thus included a
  >>column in my address table called num_of_contacts. Everytime I add a
  >>new contact, I would like to increment the num_of_contacts column in the
  >>address table.
  >>
  >>Is this possible?
 >
 >
 > Possible - yes. Good idea - no!
 >
 > Your database is designed to count those names (contacts) for you. You want
 > to form a SELECT query that will yield up your [num_of_contacts]. Done in
 > this way your number will always be correct at the moment you ask the
 > question. As a rule, you never want to store a number that you can calculate
 > on the fly from existing information. If you need help, give a few more
 > details and someone will help you design a proper SELECT query that will
 > yield the COUNT you need.
 >
 >
  >>Me thinks not (or at least, my attempts so far have failed me) so I'd
  >>appreciate it if someone could tell me if I am wasteing my time trying
  >>and instead SELECT the record, increment it, then UPDATE it.
 >
 >
 > You would be wasting your time trying to UPDATE a field from inside a SELECT
 > statement. You will have to increment it *using* a separate UPDATE
 > statement.
 > UPDATE addresses
 > WHERE {RecId} = id
 > SET num_of_contacts = num_of_contacts + 1
 >
 > Assuming you really needed to do that!
 > Thomas Bartkus
 >
 >

Thanks for that... the last bit is likely to prove more useful for me
though I understand the arguements for using such a facility.

The reason why I thought the idea might be a good idea is I could have a
table of several thousand contacts and say only five might tie to one
particular address (there is no maximum number of contact names that
might tie to an address).

If I were to perform a select on the contacts table, I would have to
search the entire table every time regardless if I was looking for
zero, one, five or fifty contact names and I didn't think that would be
a good idea. I know MySQL can handle alot more records than my database
will contain, however I was hoping to come up with a best practice
method... I mean... just because I have the resources on my system does
not mean I have to use them if I don't have to use the, true?

anyway... thanks... you've given me some food for my thoughts,
Randell D.
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

"Randell D." wrote in
message
 > Thomas Bartkus wrote:


  > >
   > >>Folks,
   > >>
   > >>I have a table of addresses and a seperate table with contact names -
   > >>All addresses tie to one or more names - I would like to keep track of
   > >>the number of names 'belonging' to an address and have thus included a
   > >>column in my address table called num_of_contacts. Everytime I add a
   > >>new contact, I would like to increment the num_of_contacts column in the
   > >>address table.
   > >>
   > >>Is this possible?
  > >
  > >
  > > Possible - yes. Good idea - no!
  > >
  > > Your database is designed to count those names (contacts) for you. You
want
  > > to form a SELECT query that will yield up your [num_of_contacts]. Done
in
  > > this way your number will always be correct at the moment you ask the
  > > question. As a rule, you never want to store a number that you can
calculate
  > > on the fly from existing information. If you need help, give a few more
  > > details and someone will help you design a proper SELECT query that will
  > > yield the COUNT you need.
  > >
  > >
   > >>Me thinks not (or at least, my attempts so far have failed me) so I'd
   > >>appreciate it if someone could tell me if I am wasteing my time trying
   > >>and instead SELECT the record, increment it, then UPDATE it.
  > >
  > >
  > > You would be wasting your time trying to UPDATE a field from inside a
SELECT
  > > statement. You will have to increment it *using* a separate UPDATE
  > > statement.
  > > UPDATE addresses
  > > WHERE {RecId} = id
  > > SET num_of_contacts = num_of_contacts + 1
  > >
  > > Assuming you really needed to do that!
  > > Thomas Bartkus
  > >
  > >
 >
 > Thanks for that... the last bit is likely to prove more useful for me
  > though I understand the arguements for using such a facility.

The rest of your message suggests otherwise. I don't think you do understand
the arguments.

 > The reason why I thought the idea might be a good idea is I could have a
 > table of several thousand contacts and say only five might tie to one
 > particular address (there is no maximum number of contact names that
 > might tie to an address).

You seem to be wrestling (unnecessarily) with a problem that anyone here
could help you with. Why not show us how you "tie to one particular
address" your contacts? What does your table structure look like?

 > If I were to perform a select on the contacts table, I would have to
 > search the entire table every time regardless if I was looking for
 > zero, one, five or fifty contact names and I didn't think that would be
 > a good idea.

Wrong! It would be a *good* idea to do exactly that which you are avoiding.
Your database is optimized precisely for this kind of problem. You *do* want
to "to
search the entire table every time regardless ...".

Where I think you are messing up is in thinking that you have to loop
through thousands of records to get your count. Not so! What you need is to
learn wise use of the COUNT() function and let MySQL do it.

 > I know MySQL can handle alot more records than my database
 > will contain, however I was hoping to come up with a best practice
 > method... I mean... just because I have the resources on my system does
 > not mean I have to use them if I don't have to use the, true?

"Best practice" means you are going to let the database return the counts
for you on the fly.

 > anyway... thanks... you've given me some food for my thoughts,

Your not eating right!
Thomas Bartkus
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

Thomas Bartkus wrote:


 >
  >>Thomas Bartkus wrote:
  >>


   >>>
   >>>
   >>>>Folks,
   >>>>
   >>>>I have a table of addresses and a seperate table with contact names -
   >>>>All addresses tie to one or more names - I would like to keep track of
   >>>>the number of names 'belonging' to an address and have thus included a
   >>>>column in my address table called num_of_contacts. Everytime I add a
   >>>>new contact, I would like to increment the num_of_contacts column in the
   >>>>address table.
   >>>>
   >>>>Is this possible?
   >>>
   >>>
   >>>Possible - yes. Good idea - no!
   >>>
   >>>Your database is designed to count those names (contacts) for you. You
 >
 > want
 >
   >>>to form a SELECT query that will yield up your [num_of_contacts]. Done
 >
 > in
 >
   >>>this way your number will always be correct at the moment you ask the
   >>>question. As a rule, you never want to store a number that you can
 >
 > calculate
 >
   >>>on the fly from existing information. If you need help, give a few more
   >>>details and someone will help you design a proper SELECT query that will
   >>>yield the COUNT you need.
   >>>
   >>>
   >>>
   >>>>Me thinks not (or at least, my attempts so far have failed me) so I'd
   >>>>appreciate it if someone could tell me if I am wasteing my time trying
   >>>>and instead SELECT the record, increment it, then UPDATE it.
   >>>
   >>>
   >>>You would be wasting your time trying to UPDATE a field from inside a
 >
 > SELECT
 >
   >>>statement. You will have to increment it *using* a separate UPDATE
   >>>statement.
   >>> UPDATE addresses
   >>> WHERE {RecId} = id
   >>> SET num_of_contacts = num_of_contacts + 1
   >>>
   >>>Assuming you really needed to do that!
   >>>Thomas Bartkus
   >>>
   >>>
  >>
  >>Thanks for that... the last bit is likely to prove more useful for me
 >
  > > though I understand the arguements for using such a facility.
 >
 > The rest of your message suggests otherwise. I don't think you do understand
 > the arguments.
 >
 >
  >>The reason why I thought the idea might be a good idea is I could have a
  >>table of several thousand contacts and say only five might tie to one
  >>particular address (there is no maximum number of contact names that
  >>might tie to an address).
 >
 >
 > You seem to be wrestling (unnecessarily) with a problem that anyone here
 > could help you with. Why not show us how you "tie to one particular
 > address" your contacts? What does your table structure look like?
 >
 >
  >>If I were to perform a select on the contacts table, I would have to
  >>search the entire table every time regardless if I was looking for
  >>zero, one, five or fifty contact names and I didn't think that would be
  >>a good idea.
 >
 >
 > Wrong! It would be a *good* idea to do exactly that which you are avoiding.
 > Your database is optimized precisely for this kind of problem. You *do* want
 > to "to
 > search the entire table every time regardless ...".
 >
 > Where I think you are messing up is in thinking that you have to loop
 > through thousands of records to get your count. Not so! What you need is to
 > learn wise use of the COUNT() function and let MySQL do it.
 >
 >
  >>I know MySQL can handle alot more records than my database
  >>will contain, however I was hoping to come up with a best practice
  >>method... I mean... just because I have the resources on my system does
  >>not mean I have to use them if I don't have to use the, true?
 >
 >
 > "Best practice" means you are going to let the database return the counts
 > for you on the fly.
 >
 >
  >>anyway... thanks... you've given me some food for my thoughts,
 >
 >
 > Your not eating right!
 > Thomas Bartkus
 >
 >

Sorry... didn't realise there was a follow on post...

I think we both have a misunderstanding... or maybe its just me...

I don't want to count the records... I want to perform a select and
have a numeric value that I can assign to LIMIT... roughly speaking,
this is why:

table:address
It contains numerous addresses, no names - each address has a hash
(There's a reason as to why I'm not using unique numeric IDs... long
story but ignore it for now).

table:contacts
It contains names, and a cell for address_hash. Thus for each name, I
will have an address to tie it to. I permit no limit on names that can
be tied to a single address.

My 'problem':
I retrieve an address and want to find out all the names that are tied
to it... I know I could just do:

SELECT contacts.firstname,contacts.lastname,address.line_1
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

However since my select does not know how many contact names are tied to
the address, it will search through the entire contacts table, true?

If I was to suffix the above query with a LIMIT condition, this would
help, true? The problem is, I don't know how many names *might* be
reitrieved from the contacts table hence why it would be useful if I had
a value for addres.num_of_contacts, as I could use its value as part of
my LIMIT criteria.

True/False?

Have you an alternative solution?

Thanks for the (constructive) criticism - I'm doing MySQL for about a
year plus now though not in a working environment so I'm open to all
comments that help improve my skillset.

Cheers
Randell D.
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

Randell D. wrote:
 > I retrieve an address and want to find out all the names that are tied
 > to it... I know I could just do:
 >
 > SELECT contacts.firstname,contacts.lastname,address.line_1
 > FROM contacts,address
 > WHERE contacts.address_hash='$myhashkey'
 > AND address.hash='$myhashkey';

I would write this query slightly differently:

SELECT c.firstname, c.lastname, a.line_1
FROM contacts AS c INNER JOIN address AS a
ON c.address_hash = a.hash
WHERE a.hash = '$myhashkey'

You don't need to worry about the number of contacts that match the
address. The query above returns only those entries that match, whether
that is zero, 1, or more.

You should put indexes on c.address_hash and a.hash, so MySQL can look
up these values quickly using the indexes. MySQL doesn't have to read
the whole table that way.

You're concerned about the efficiency and performance of the query, and
that's good, but be smart about where you focus your efforts; don't
solve performance problems until you can prove that they exist and are
in fact leading to a performance bottleneck in your application.
There's no sense making work for yourself to solve one performance
issue, if other parts of the application are still going to be the
limitation on performance. For what it's worth, I have yet to find any
simple query on a dataset of a few thousand rows become a bottleneck;
it's more likely that the routine that outputs the result takes more time.

Also, I agree with the other folks that storing the number of contacts
per address is not recommended. There's too much risk that this value
might become out of sync with the state of the data. Incorrect results
are far worse than slow performance.

Regards,
Bill K.
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

"Randell D." wrote in
message
 >
 > I don't want to count the records... I want to perform a select and
 > have a numeric value that I can assign to LIMIT... roughly speaking,
 > this is why:

You go on but you never do explain why - speaking roughly or otherwise.
What in heck kind of number do you need to give to LIMIT? What do you
expect it to do for you?

If you simply needed to paginate, LIMIT does it all
LIMIT 25, StartRecNo
gets you 25 records at a time if you simply increment [StartRecNo] by 25
each iteration through your paginating loop.

BUT That's just a guess because you never do state what it is you are trying
to achieve!

 > table:address
 > It contains numerous addresses, no names - each address has a hash
 > (There's a reason as to why I'm not using unique numeric IDs... long
 > story but ignore it for now).

Good! I'm ignoring

 > table:contacts
 > It contains names, and a cell for address_hash. Thus for each name, I
 > will have an address to tie it to. I permit no limit on names that can
 > be tied to a single address.

So what?
You seem to think this is some kind of big deal, but I see nothing other
than routine database stuff here.

 > My 'problem':
 > I retrieve an address and want to find out all the names that are tied
 > to it... I know I could just do:
 >
 > SELECT contacts.firstname,contacts.lastname,address.line_1
 > FROM contacts,address
 > WHERE contacts.address_hash='$myhashkey'
 > AND address.hash='$myhashkey';

Okay!

 > However since my select does not know how many contact names are tied to
 > the address, it will search through the entire contacts table, true?

True. But why is this at all bothersome?
Are you not indexing?
MySQL was designed to eat problems like this for lunch.

Why/how would SELECT know or care how many records there are ahead of it.
It doesn't! The whole point of SELECT is to find out for heavens sakes!

 > If I was to suffix the above query with a LIMIT condition, this would
 > help, true?

Help what? What can this possibly do for you?
If you have a reason *you* can't accept an indeterminate flood of records,
then by all means LIMIT. I assure you, MySQL won't care one way or the
other.

But your question "would this help?" is indecipherable.
It won't help you get a date. It won't help you make money. It won't improve
your health

AND it won't help MySQL do it's job!

 > The problem is, I don't know how many names *might* be
 > reitrieved from the contacts table hence why it would be useful if I had
 > a value for addres.num_of_contacts, ...

If *you* need to know "how many names *might* be reitrieved from the
contacts table" then it is incumbent on *you* to ask.

SELECT COUNT(contacts.*) as NumberOfNames
FROM contacts,address
WHERE contacts.address_hash='$myhashkey'
AND address.hash='$myhashkey';

MySQL won't mind if you ask - honest!

 > as I could use its value as part of my LIMIT criteria.
 > True/False?

In order to achieve *what* ?
If you were expecting it to somehow make your query more efficient, the
answer is most certainly False.

 > Have you an alternative solution?

Heck - I still don't even have the problem.

 > Thanks for the (constructive) criticism - I'm doing MySQL for about a
 > year plus now though not in a working environment so I'm open to all
 > comments that help improve my skillset.

Hey - I'm trying!
Thomas Bartkus
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

Bill Karwin wrote:

 > Randell D. wrote:
 >
  >> I retrieve an address and want to find out all the names that are tied
  >> to it... I know I could just do:
  >>
  >> SELECT contacts.firstname,contacts.lastname,address.line_1
  >> FROM contacts,address
  >> WHERE contacts.address_hash='$myhashkey'
  >> AND address.hash='$myhashkey';
 >
 >
 > I would write this query slightly differently:
 >
 > SELECT c.firstname, c.lastname, a.line_1
 > FROM contacts AS c INNER JOIN address AS a
 > ON c.address_hash = a.hash
 > WHERE a.hash = '$myhashkey'
 >
 > You don't need to worry about the number of contacts that match the
 > address. The query above returns only those entries that match, whether
 > that is zero, 1, or more.
 >
 > You should put indexes on c.address_hash and a.hash, so MySQL can look
 > up these values quickly using the indexes. MySQL doesn't have to read
 > the whole table that way.
 >
 > You're concerned about the efficiency and performance of the query, and
 > that's good, but be smart about where you focus your efforts; don't
 > solve performance problems until you can prove that they exist and are
 > in fact leading to a performance bottleneck in your application. There's
 > no sense making work for yourself to solve one performance issue, if
 > other parts of the application are still going to be the limitation on
 > performance. For what it's worth, I have yet to find any simple query
 > on a dataset of a few thousand rows become a bottleneck; it's more
 > likely that the routine that outputs the result takes more time.
 >
 > Also, I agree with the other folks that storing the number of contacts
 > per address is not recommended. There's too much risk that this value
 > might become out of sync with the state of the data. Incorrect results
 > are far worse than slow performance.
 >
 > Regards,
 > Bill K.


Thanks for re-writing the query with the inner join - Joins are new to
me and I'm only just begining to understand it.

I'm glad you can see that I was concentrating on the efficiency and
performance of the query - but I also understand your arguements as to
why I might well be wasteing my time. I'll leave off it for the moment
and investigate it at a later stage.

Thanks,

randelld
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

Thomas Bartkus wrote:



 >
  >>I don't want to count the records... I want to perform a select and
  >>have a numeric value that I can assign to LIMIT... roughly speaking,
  >>this is why:
 >
 >
 > You go on but you never do explain why - speaking roughly or otherwise.
 > What in heck kind of number do you need to give to LIMIT? What do you
 > expect it to do for you?
 >
 > If you simply needed to paginate, LIMIT does it all
 > LIMIT 25, StartRecNo
 > gets you 25 records at a time if you simply increment [StartRecNo] by 25
 > each iteration through your paginating loop.
 >
 > BUT That's just a guess because you never do state what it is you are trying
 > to achieve!
 >
 >
  >>table:address
  >>It contains numerous addresses, no names - each address has a hash
  >>(There's a reason as to why I'm not using unique numeric IDs... long
  >>story but ignore it for now).
 >
 >
 > Good! I'm ignoring
 >
 >
  >>table:contacts
  >>It contains names, and a cell for address_hash. Thus for each name, I
  >>will have an address to tie it to. I permit no limit on names that can
  >>be tied to a single address.
 >
 >
 > So what?
 > You seem to think this is some kind of big deal, but I see nothing other
 > than routine database stuff here.
 >
 >
  >>My 'problem':
  >>I retrieve an address and want to find out all the names that are tied
  >>to it... I know I could just do:
  >>
  >>SELECT contacts.firstname,contacts.lastname,address.line_1
  >>FROM contacts,address
  >>WHERE contacts.address_hash='$myhashkey'
  >>AND address.hash='$myhashkey';
 >
 >
 > Okay!
 >
 >
  >>However since my select does not know how many contact names are tied to
  >>the address, it will search through the entire contacts table, true?
 >
 >
 > True. But why is this at all bothersome?
 > Are you not indexing?
 > MySQL was designed to eat problems like this for lunch.
 >
 > Why/how would SELECT know or care how many records there are ahead of it.
 > It doesn't! The whole point of SELECT is to find out for heavens sakes!
 >
 >
  >>If I was to suffix the above query with a LIMIT condition, this would
  >>help, true?
 >
 >
 > Help what? What can this possibly do for you?
 > If you have a reason *you* can't accept an indeterminate flood of records,
 > then by all means LIMIT. I assure you, MySQL won't care one way or the
 > other.
 >
 > But your question "would this help?" is indecipherable.
 > It won't help you get a date. It won't help you make money. It won't improve
 > your health
 >
 > AND it won't help MySQL do it's job!
 >
 >
  >>The problem is, I don't know how many names *might* be
  >>reitrieved from the contacts table hence why it would be useful if I had
  >>a value for addres.num_of_contacts, ...
 >
 >
 > If *you* need to know "how many names *might* be reitrieved from the
 > contacts table" then it is incumbent on *you* to ask.
 >
 > SELECT COUNT(contacts.*) as NumberOfNames
 > FROM contacts,address
 > WHERE contacts.address_hash='$myhashkey'
 > AND address.hash='$myhashkey';
 >
 > MySQL won't mind if you ask - honest!
 >
 >
  >>as I could use its value as part of my LIMIT criteria.
  >>True/False?
 >
 >
 > In order to achieve *what* ?
 > If you were expecting it to somehow make your query more efficient, the
 > answer is most certainly False.
 >
 >
  >>Have you an alternative solution?
 >
 >
 > Heck - I still don't even have the problem.
 >
 >
  >>Thanks for the (constructive) criticism - I'm doing MySQL for about a
  >>year plus now though not in a working environment so I'm open to all
  >>comments that help improve my skillset.
 >
 >
 > Hey - I'm trying!
 > Thomas Bartkus
 >
 >


I'll leave it - I cannot explain it any more clearer than I have done
already - I know MySQL is geared towards handling the data, but as
another reply to my post found, I was merely trying to assist MySQL in
performing the query with greater efficiency. I do have indexes, and I
didn't want MySQL to have to investigate every hash in order to find
every name that tie's to my address. The way I see it, if I had one my
contacts table with 20,000 records, and if I had a record that had one
name, it would still examine everyone of the hashes for my contacts
table *in case* there were more names. I had hoped that by being able
to have a LIMIT in my select, then once the record is found in the
contacts hash table, it would return straight away as opposed to
continue searching. But because any number of names can be tied to an
address, the LIMIT value will vary... In some cases, the address table
may not have a name tied to it therefore without using LIMIT, I'd end up
searching the contacts table and waste resources.

I hope you understand the above... if not... then... well...

lets just leave it...

Thanks for taking the time/effort though,

Randell D.
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

Randell D. wrote:
 > if I had one my
 > contacts table with 20,000 records, and if I had a record that had one
 > name, it would still examine everyone of the hashes for my contacts
 > table *in case* there were more names.

That's why you would use indexes on the address fields. An index is
sorted, so MySQL can look up values very quickly. So don't feel too bad
about making MySQL work! It's really not a problem here.

 > I had hoped that by being able
 > to have a LIMIT in my select, then once the record is found in the
 > contacts hash table, it would return straight away as opposed to
 > continue searching.

Yes, this is an optimization that LIMIT can perform (see
<a rel="nofollow" style='text-decoration: none;' href="http://dev.mysql.com/doc/mysql/en/limit-optimization.html" target="_blank">http://dev.mysql.com/doc/mysql/en/limit-optimization.html</a>), but as you
have already realized, you must LIMIT by a fixed number, which makes it
hard to use in this case.

Regards,
Bill K.
 >> Stay informed about: With INSERT can I increment an existing value in a column? 

Bill Karwin wrote:
 > Randell D. wrote:
 >
  >> if I had one my contacts table with 20,000 records, and if I had a
  >> record that had one name, it would still examine everyone of the
  >> hashes for my contacts table *in case* there were more names.
 >
 >
 > That's why you would use indexes on the address fields. An index is
 > sorted, so MySQL can look up values very quickly. So don't feel too bad
 > about making MySQL work! It's really not a problem here.
 >
  >> I had hoped that by being able to have a LIMIT in my select, then once
  >> the record is found in the contacts hash table, it would return
  >> straight away as opposed to continue searching.
 >
 >
 > Yes, this is an optimization that LIMIT can perform (see
 > <a rel="nofollow" style='text-decoration: none;' href="http://dev.mysql.com/doc/mysql/en/limit-optimization.html" target="_blank">http://dev.mysql.com/doc/mysql/en/limit-optimization.html</a>), but as you
 > have already realized, you must LIMIT by a fixed number, which makes it
 > hard to use in this case.
 >
 > Regards,
 > Bill K.


Thanks again...

randelld
 >> Stay informed about: With INSERT can I increment an existing value in a column?