CODEWITHSUNDEEP
phpmysqldatabaseandroid-sqlite
Mansukh Ahir
Mansukh Ahir

Reputation: 3553

How can manage daily 5000 record insert update in mysql?

I am creating school attendance project using PHP MYSQL and there are 5000 student in school. Daily 5000 record are inserted in database So yearly (500x365) = 1825000 records of 1 year.

Everyday mysql database records size increase so how can manage more data. Daily like Absent, Present, Leave ect. status are inserting.

So please help me how can do this best way using PHP MYSQL and easily manage millions of records.

Upvotes: 2

Views: 430

Answers (1)

O. Jones
O. Jones

Reputation: 108736

Two million (20 lahks of) rows per year is well within the capabilities of MySQL, or any competent RDMS. We usually don't start using the word "huge" until we get near a billion (100 crore of) records.

But you still have an interesting problem. You're launching a new application, with a very few records in your database. Over the first weeks and months your data base will stop being "small" and start being "medium-sized" as your application is used.

Here's what you can expect as that happens: Some queries in your application will become slower as the database grows. You need to be vigilant about that, and identify the slow queries. MySQL servers offer a slow query log to help with that. You can read about how to use it.

When you identify slow queries, you can then use the EXPLAIN command to give you hints about why they are slow. Slowness in a growing application is usually because you need to add indexes to some of your tables. Just do that. It's probably best to add indexes during off-peak hours. Shortly after you do daily backups of your tables is a good time to do that. (You ARE doing daily backups, correct?)

If you add the correct choice of index, your slow query will stop being a slow query. That does not mean you can stop being vigilant: the next phase of growth will most likely identify another slow query.

In the lingo of computer science, some queries in your application take O(n) time, where n is the number of rows in some table. Others will take O(n-squared) or worse. When n is a small number, you simply don't notice this. But as n grows, these queries become bottlenecks for your users' performance. DBMS software is very good at coping with this kind of inefficiency by using indexes on tables. So, you need to add the necessary indexes when you identify the bottleneck queries.

It is very difficult, even for highly experienced people, to predict which queries will become slow as a new application grows. At the same time, adding a lot of indexes as insurance against slowdowns doesn't usually help. So this need for vigilance is not a failing on your part: it's simply part of operating a database application.

Some companies and organizations employ database administrators (DBAs) to do this kind of work. If you have access to a DBA, take advantage of that.

In the meantime, read http://use-the-index-luke.com/ to get an understanding of how these indexes work.

And, congratulations on launching an application that will grow! It's big deal.

Upvotes: 3

Related Questions