An experimentation with Django Models

Single big table × Multiple small tables

Context:

UserProfile tables contain one single field, name, which will contain a random 25 characters string.

UserProfileRoot is the big table and it has 100000 entries.
UserProfileRoot was then distributed in 10 different tables containing 10000 entries each, with the exact same and respective records:

UserProfile1
UserProfile2
...
UserProfile9
UserProfile10

The name variable was retrieved from the UserProfileRoot through a randomly generated pk ranging betweeen 0 and 100000.
(To maintain randomness)

The variable name will be used to query the database and to perform the test.

if UserProfileRoot.objects.filter(name = name).exists():
     user = UserProfileRoot.objects.get(name = name)

The same pk that was previously randomly generated is used here.
The same variable name will be used to query the database and to perform the test.

if pk <= 10000:
    if UserProfile1.objects.filter(name = name).exists():
        user = UserProfile1.objects.get(name = name)

if pk > 10000 and pk <= 20000:
    if UserProfile2.objects.filter(name = name).exists():
        user = UserProfile2.objects.get(name = name)

if pk > 20000 and pk <= 30000:
    if UserProfile3.objects.filter(name = name).exists():
        user = UserProfile3.objects.get(name = name)

if pk > 30000 and pk <= 40000:
    if UserProfile4.objects.filter(name = name).exists():
        user = UserProfile4.objects.get(name = name)

if pk > 40000 and pk <= 50000:
    if UserProfile5.objects.filter(name = name).exists():
        user = UserProfile5.objects.get(name = name)

if pk > 50000 and pk <= 60000:
    if UserProfile6.objects.filter(name = name).exists():
        user = UserProfile6.objects.get(name = name)

if pk > 60000 and pk <= 70000:
    if UserProfile7.objects.filter(name = name).exists():
        user = UserProfile7.objects.get(name = name)

if pk > 70000 and pk <= 80000:
    if UserProfile8.objects.filter(name = name).exists():
        user = UserProfile8.objects.get(name = name)

if pk > 80000 and pk <= 90000:
    if UserProfile9.objects.filter(name = name).exists():
        user = UserProfile9.objects.get(name = name)

if pk > 90000 and pk <= 100000:
    if UserProfile10.objects.filter(name = name).exists():
        user = UserProfile10.objects.get(name = name)

Conclusion

Both queries were looped 100000 times.
Theres is a significative performance difference.
 

The average query time for the big table was:
26.5 milliseconds

The average query time for the small tables was:
3.8 milliseconds