- Introduction
- Description
- Database ERD
- Experiment Queries
- Hardware Characteristics
- Recommendations
- References
- Contributors
- License
In real world situations, creating database projects is more just making a scheme that fits the description. We are usually faced with a lot of questions like:
- What DBMS should we use?
- Is it better to make it relational or NOSQL?
- How to get the best performance?
- What Hardware spec. are recommended to use?
In this Repository we experiment with the different concepts of database tuning and the different techniques used like:
- Optimizing the schema.
- Optimizing the memory requirements.
- Optimizing the queries.
- Testing the performace of MySQL vs MongoDB.
-
Select ID, Name, and Gender of students who got an
"A"
in a course final exam. (Schema Optimization was applied
) -
Select all the research projects worked on by professors working in a certain department along with the professors’ names. (
Cache was used
) -
Select ID, Name, Gender, and Salary of all professors with a salary less than
1200
. (B-Tree Index was used
) -
Select ID, Name, Gender, and Salary of a certain professor using his name. (
Hash Index was used
) -
Select ID, Name, Gender, and Salary of all professors with Certain Name whose Salary is greater than
6000
. (Hash Index and B-Tree were used
) -
Select student ID, Name, and City of students with a specific name or who live in a specific city. (
Query Optimization was used
)
- OS: Windows 10 64-bits
- CPU: Intel® Core™ i7-8750 CPU @2.20GHz (12 CPUs)
- RAM: 16 GB
- Hard disk: 1 TB HDD
-
Using indexing in MySQL improves the queries execution time significantly at the cost of additional memory and slower updates.
-
Taking your time to design the schema is a very important step in database design as we showed how schema optimization significantly improves the execution time of the queries involved in that optimization.
-
Using SQL is better than NOSQL in complex queries that join multiple tables together, while NOSQL is better in retrieving a huge number of records from the same collection so when designing the NOSQL schema we should consider Embedding rather than referencing whenever possible.
-
Using caching significantly decreases the execution time of frequently used selection queries. So we recommend using caching whenever possible but note that when data is too large you need a very large cache to have that significant speedup.
- MySQL Shell
- DBSchema
- ERD Plus
- MySQL 5.7
- MongoDB 4.4
- Abstract vector created by vectorjuice - www.freepik.com
Muhammad Ahmad Hesham |
Muhammad Alaa |
- Licensed under the MIT License.