Best practice named querry


We are building a application to register the part made on 13 machine. We would like to search the amount of part made for each BT from the machine part. Now it's working well but i worry when the db will get bigger it will creat some slow down maybe i am wrong i dont have a lot of experience deling with large DB. The DB size is around 90 000 entry per year we will flush after 5 year.

Because i like to display the number of part every minute and it's working very well with 5000 value in the DB.

So my question is running a querry every minute in a table that have 400 000 line is ok or i should do something to uptimise it.

Thank You

I would recommend reading up on indexes. They can help your query become more performant. Though you should not just throw them everywhere willy nilly. Look up how to view the execution of your query first via EXPLAIN with MySQL or whatever your particular DB uses. This will help you figure out what kind of index to use. Though usually it is just as simple as putting index on the column(s) you are using in your WHERE clause.

Running a query every minute with 400,000 rows seems fine as long as your db is saying it is fine - how long does it take to execute? If you're in milliseconds which I would think you should be even in an unoptimized db if it's got decent hardware, I think you are fine (though still look into indexes). If it's a couple of second then yea could take longer down the road but my advice would be the same - explain the query, see where an index would help, then apply it.

1 Like

Thx for the Answer now i am only at 5000 so i am still in millisecond. i will look into index

Consider use a script to populate your database with a million rows of pseudo-random data, using patterns similar to what your expect for real use.

Then tests your queries and indices.