Design decision 2007-10-03 - By Brent Baisley
Back
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?
> unsub=brenttech@(protected)
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql@(protected)
|
|
