do database size affect insert time in mysql?

Question

I want to log my users' logins so I created a database like this :

CREATE TABLE `Login` (
    `UserId` INT(11) NOT NULL,
    `Ip` BIGINT(20) NOT NULL,
    `Date` DATETIME NOT NULL
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;

With no trigger. no key. no index.

Insertion code is :

INSERT INTO `Login` (`UserId`, `Ip`, `Date`) VALUES ({userId}, INET_ATON({userIp}), NOW());

With some calculation iI know it will get big. Does large data affect insertion time ? With large data I mean more than 3 million record.

I don't know it well enough to give an answer, but I was able to dig this up out of my [bookmarks](http://dba.stackexchange.com/questions/22888/how-database-size-affects-performance-theory-vs-reality). — AdamMc331, Oct 21 '14 at 16:05
@McAdam331 i saw this. it says 'not a big consideration' well. i need some numbers. for example if insertion to an empty table take 10ms how much time it take to insert to a big table (>3m records)? — Pedram A, Oct 21 '14 at 16:11
Well, if you want a specific number, I would write a small application with a 3mil loop to insert junk data, and then insert a new row to find out. I believe MySQL gives you the time required. — AdamMc331, Oct 21 '14 at 16:13

score 2 · Accepted Answer · edited May 23 '17 at 11:45

2

I think you could use this previous question here:

Quoted from the top answer:

The physical database size doesn't matter. The number of records don't matter. In my experience the biggest problem that you are going to run in to is not size, but the number of queries you can handle at a time

edited May 23 '17 at 11:45

Community

1
1

answered Oct 21 '14 at 16:09

Guillermo Oramas R.

1,303
2
14
31

Which means that with the time, the size will affect performance, does not it? – Yasen Zhelev Oct 21 '14 at 16:35

score 1 · Answer 2 · answered Oct 21 '14 at 16:38

1

Lets use the MySQL site to answer this question http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

Because you do not have indexes, you will not notice performance degradation with the size increment.

answered Oct 21 '14 at 16:38

Yasen Zhelev

4,045
3
31
56

update refman link: https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html – SubmarineX Aug 13 '20 at 03:14

do database size affect insert time in mysql?

2 Answers2