Mysql – Database size and cycle

database-sizeMySQL

I am developing a desktop application and it will include clients, bills, banks, incomes&outgoings etc,

I am using mysql and delphi,

My question is do i have to cycle (create new tables and transfer needed data from old tables to new tables for new year) every year because i think database will have data for a year and it will adventually get slow, is there a way/is it possible to continue without cycle but without database get slow?

I want to do this because i want user be able to find/see client cards and bills of old years too

Thanks

Best Answer

With rare exceptions, do not create identical tables split by year or month or customer or whatever. If a table is getting slower as it gets bigger, then you don't have the right indexes and/or you need to redesign the queries. Even a billion rows in a table is not 'unmanageable'.

Clients, bills, banks, transactions -- sounds like about 4 tables, plus some tables for Normalization. A bill might contain a client_id column, but not the client's name, address, etc.

Related Solutions

Sql-server – PostgreSQL Initial Database Size

No the only thing close to that is when you compile the server with the --with-segsize switch, this might help if your table is taking up more space than a gig and your file system can handle a single file being over a gig. If your inserting 20 gigs it will have to create 20 files if you don't use this switch. If your file system can handle a file over a gig you can just set it to a large value most likely see some benefit, worst case a small benefit.
Take a look at CLUSTER http://www.postgresql.org/docs/9.1/static/sql-cluster.htmland FILLFACTOR http://www.postgresql.org/docs/9.1/static/sql-createtable.html, http://www.postgresql.org/docs/9.1/static/sql-createindex.html

Note that FILLFACTOR can be applied to both tables and indexes.

Mysql – Database and query optimizacion

Further to the earlier discussion and the answer offered by @Frustrated, here is what I came up with for a Stored Procedure:

CREATE PROCEDURE insertNumber(IN newNum VARCHAR(11))
BEGIN
    DECLARE i int;
    SELECT COUNT(*) INTO i
        FROM
        (
            SELECT `number` FROM `tbl_blacklist` WHERE `number`=newNum
            UNION
            SELECT `number` FROM `tbl_cmdatabase`  WHERE `number`=newNum
        ) AS t1;
    IF i = 0 THEN
        INSERT INTO `tbl_database` (`number`) VALUES (newNum);
    END IF;
END;

I tested it under MySQL 5.5.29 by issuing commands like CALL insertNumber('123-1234'); and it seemed to work just fine.

Or, perhaps it might be more convenient to put that same logic in an INSERT trigger instead:

CREATE TRIGGER checkLists 
BEFORE INSERT ON `tbl_database`
FOR EACH ROW
BEGIN
    DECLARE i int;
    SELECT COUNT(*) INTO i
        FROM
        (
            SELECT bl.`number` FROM `tbl_blacklist` bl
                WHERE bl.`number`=new.`number`
            UNION
            SELECT cm.`number` FROM `tbl_cmdatabase` cm
                WHERE cm.`number`=new.`number`
        ) AS t1;
    IF i > 0 THEN
        SIGNAL SQLSTATE '45000';  # unhandled user-defined exception
    END IF;
END;

Obviously, the number column should be indexed in both the tbl_blacklist and tbl_cmdatabase tables.

Best Answer

Related Solutions

Sql-server – PostgreSQL Initial Database Size

Mysql – Database and query optimizacion

Related Question