How to avoid performance problems with tables that contain a lot of data?

Asked

Viewed 62 times

1

I am developing a payroll system, in which there is a "problem' in one of its tables.
Because, to control the discounts on the holerite of each employee, I thought to create a table with the name: Discounts that would have the fields, as: id,day/time,guy,discounted,`notes. However, this table will in the future have many records due to the company having 150 employees

Obs: How I could better shape that part of the system to avoid long-term problems?

php mysql sql query
  • 1

    Search for the use of indexes (https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html)

  • 1

    add indexes to your database, which make tables with millions of records run faster without losing performance

  • You can also in addition to using indexes as friends above spoke, you can move data from months ago to a second table.

  • What @Iagoleão said is a good one and I implemented so, every end of month, automated my system to save in another table everything that was done in this month, and erases the previous month, so its production table, beyond the indexes already put, will have less stress to work.

  • I’ll follow the tips, thank you very much.

1 answer

1


Is the data modeling of the table well done? Because depending on the table construction you can replace the type as "TIPOABCDE" to 1, minimizing the amount of bytes per record and consequently decreasing the search time in the table.

Also see the Standard Your tables are. Leave in the main table only indexes referencing relationships with other tables, avoiding leaving too much load in a single table making it too heavy.

2º Would the 'problem' be performance? The problem would occur during a query in a table or a report?

In this case maybe the problem is not in a large amount of data in the table, so an auxiliary column content month/indexed year could speed up the search depending on the query, or simply trying to fix it if there are bad joins (which ends the bank’s performance)

Another way to shorten processing time is to use "subquery factoring" techniques, which if well used can greatly decrease the time of a query

3º The machine where this processing system is made is dedicated? Otherwise you may have performance problems not because of your system, but because of competition from this system with other machine.

Browser other questions tagged php mysql sql query

You are not signed in. Login or sign up in order to post.