I'm not sure why you say 30 or more inserts will take too long. As
long as you do a bulk insert, it's just a single command. 30
individual insert will take it's toll.
You are really looking for a logging system. Your not going to be
querying the table all that much, just a lot of inserts. So millions
and millions of rows isn't that big of a deal. Your not deleting
anything either, so if you set your parameters right, you can use
MyISAM tables without locking issues. You need to set the appropriate
parameter to only insert to the end of the table.
When your table reaches a certain size (50 million?), you rename it a
create an empty one. If you need to query multiple tables after you
have a bunch, just create a merge table. I've done a similar setup on
a system that added 5-7 million records per day.
Alternatively, had a text field that logs all the keywords and a word
count field that tells you how many words were entered. You would
need to parse the words or use full text indexing to perform
analysis, but that would be a common thing I'm guessing.
On Oct 3, 2007, at 3:57 AM, Scott Haneda wrote:
I have an a table of objects, attached to those objects are keywords.
Users submit the keywords to the objects.
Currently, I chose to keep a hit count on the keywords, so if a
duplicate
keyword is supplied, a counter is incremented.
I thought this was a good idea, as it keeps the number of rows in the
keywords table to a minimum.
However, this is a user login based system, and with the above, I
lose the
ability to track which users sent in which keywords.
So I can move to the keywords table storing duplicate keywords, and
each
keyword will get a user_id attached to it, but, that table will
grow. Fast.
1000 objects, each with 10 keywords only = 10,000, I could hit many
millions
very fast.
I could toss in a third table, and relate that to the user_id and
keyword.
However, I am inserting in one go, and that could be upwards of 30
or more
inserts, taking too long.
Anyone got any suggestions? Thanks.
--
-------------------------------------------------------------
Scott Haneda Tel: 415.898.2602
<http://www.newgeo.com> Novato, CA U.S.A.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
