Hello,
I have 3 years of experience in web technology, working on warehouse management system, it's a product and have a million of products and number of stores.
1) are we following best structure in terms of Database which ensure scalility e.i 1 million users.
=> Need to show your database, i am working on a product for WMS(warehouse management system), we are handling millions of products and numbers of stores at different location. We manage it very nicely by our coding practice like use indexing, keep table shorter, not much column in a table(proper normalization) etc...
2) is our database optimized enough and will server our expansion strategy
=> for the optimization need to check database structure it is normalized or not, proper index given to particular column in each table or not, indexing is an effective way to tune your SQL database that is often neglected during development, etc...
It's depend not only database structure but also how query written, query must be write by making use of index field, so write optimize query is also important because if we design database properly but not make use of PL/SQL standards then it's performance is too slow.
Some of key points to optimize database
=========================
Indexes
Execution Plans
Tune SQL query performance by avoiding coding loops
Avoid correlated sub-queries
Select sparingly(avoid SELECT *)
The use of temp tables
OR there are many other topics that need to keep in mind for developer and database administrator.
3) how could we improve our structure and what technology we need to adopt in case of changes and what the change we expect
=> If DB is not normalized then need to change it, it may change in structure and this will add some more tables, indexes, parent-child table etc...
4) run a performance test on db, how to do it to make sure we're doing well?
=> for that when developer write any query, first need to describe it and analyse execution time and memory it take and how much records it fetch and need to decrease execution time and fetch only required data or fields.
5) is our server suitable for our strategy? do we have to move to cloud?
=> Its totally depends on network speed company provided and configuration plan what we select.
For optimization need to check below points
================
normalization
do proper indexing, this will fetch data more faster than normal column. so make practice to use indexing column in where clause.
primary key => foreign key relation
Query optimization like use UNION instead of OR etc.
Also manage below buffering in mysql configuration file(some server manage it himself not need to do maual setting like AWS)
=============================
join buffer size
key buffer size
sort buffer size
cache buffer size
--
Savshit Mavani
8087075078