Lets say I want to store songs in my database. Instead of having just one
Song table, I have ten tables. The
Song table also has a foreign key to the
Artist table. When an artist is added to the database, we check exactly how many songs are there in each of the
Song tables, and we assign the table with the smallest number of songs to the artist. All of the artist's songs will be stored in that
I don't want to store 1M objects in one table, but rather divide them into ten pieces, each of around 100k objects, and store these pieces in ten different, but structurally similar tables. Now, provided that the song table reference in the artist objects are never changed, will my overall system be faster and have better performance?
I realize one big issue would be finding individual songs, but please answer this question under the context that songs can only be retrieved from the database by providing 2 parameters:
If I have the artist_id, I can use that to get my artist object, which contains a reference to the song table that contains the song with the song with the song_id give. So I don't have to query ten different tables for finding a song, if I have the artist_id, which will always be the case.
Is this going to be completely useless? Or will it have a positive impact on my system's performance?
Note: I realize songs should never be stored this way, since you would want to query songs without knowing the artist, but this is only for an example, albeit a poor one. Also, please ignore the fact that these would be a mess to code and manage when answering. I only want to know about the performance impacts.