When setting up the Islam Archive project, it was important to me to keep the database as small as possible. But now I’ve learned that you can not keep the database small without consequences on such large projects. Either you will keep it small or burden it more. The important thing here is to find the harmony and not to fall too much on one side.
Database balance
I have the following tables in the Islam Archive database, which are important here:
- parents
- parent_metas
- childs
- child_metas
- grandchilds
- grandchild_metas
- tags
- tag_metas
- tags_junction
So far, I have done this so that when a child is retrieved from the database, several values are automatically retrieved. Among other things, the number of “tags_junction” where this child record is mentioned. This means that this query is executed 114 times on the Koran page because there are 114 sura (childs). The query is then in an example as follows:
SELECT COUNT(DISTINCT tj.tag_id) FROM tags_junction tj INNER JOIN grandchilds gc ON tj.grandchild_id = gc.id WHERE gc.parent_type = "child" AND gc.parent_id = 1This has meant that the database on the current server is very busy and took a long time until the answer came from the server. The result as follows:
The total load time of the website was almost 10 seconds:
Of course that was unbearable and unbalanced. The problem was that I only collected the grandchild_id’s in the tags_junction table and skipped child_id’s and parent_id’s. Now I have the columns child_id and parent_id also added and filled so that I can count the records without INNER JOIN’s at the end.
The balance here is that I have increased the table from 3 to 5 columns in return, but the query has shortened by about 85 percent. So from 9.5 seconds to 1.5 seconds.
