CODEWITHSUNDEEP
mysql
rda3mon
rda3mon

Reputation: 1719

Alternate to storing Large number of tables -- MySQL

Well, I have been working with large amount of network data. In which I have to filter out some IP address and store their communication with other IP's. But the number of IP's are huge, hundreds of thousands, for which I have to create so many tables. Ultimately I my MySQL access will slow down, everything will slow down. Each table will have few columns, many rows.

My Questions:
Is there a better way to deal with this, I mean storing data of each IP?
Is there something like table of tables?

[Edit] The reason I am storing in different tables is, I have to keep removing and add entries as time passes by.
Here is the table structure
CREATE TABLE IP(syn_time datetime, source_ip varchar(18), dest_ip varchar(18));

I use C++ to access with ODBC connector

Upvotes: 2

Views: 554

Answers (2)

Nathan
Nathan

Reputation: 11149

Creating tables dynamically is almost always a bad idea. The alternative is normalisation. I won't go into the academic details of that, but I'll try to explain it in more simple terms.

You can separate relationships between data into three types: one-to-one, one-to-many and many-to-many. Think about how each bit of data relates to other bits and which type of relationship it has.

  • If a data relationship is one-to-one, then you can usually just stick it in the same row of the same table. Occasionally there may be a reason to separate it as if it were one-to-many, but generally speaking, stick it all in the same place.

  • If a data relationship is one-to-many, it should be referenced between two tables by it's primary key (you've given each table a primary key, right?). The "one" side of one-to-many should have a field which references the primary key of the other table. This field is called a foreign key.

  • Many-to-many is the most complex relationship, and it sounds like you have a few of these. You have to create a join table. This table will contain two foreign key fields, one for one table and another for the other. For each link between two records, you'll add one record to your join table.

Hopefully this should get you started.

Upvotes: 0

servermanfail
servermanfail

Reputation: 2538

  • Don't DROP/CREATE tables frequently. MySQL is very buggy with doing that, and understandably so--it should only be done once when the database is created on a new machine. It will hurt things like your buffer pool hit ratio, and disk IO will spike out.

  • Instead, use InnoDB or xtradb, which means you can delete old rows whilst inserting new ones.

  • Store the IP in a column of type int(10) unsigned e.g. 192.168.10.50 would be stored as (192 * 2^24) + (168 * 2^16) + (10 * 2^8) + 50 = 3232238130

  • Put all the information into 1 table, and just use an SELECT ... WHERE on an indexed column

Upvotes: 1

Related Questions