Easy way to observe user activity - how improve my database structure.

Question

Welcome,

I need some advise to improve perfomence my web application.

In the begin I had this structure of database:

USER

-id (Primary Key)

-name

-password

-email ....

PROFILE

-user Primary Key, Foreign Key (USER)

-birthday

-region

-photoFile ...

PAGES

-id (Primary Key)

-user Foreign Key(USER)

-page

-date

COMMENTS

-id (Primary Key)

-user Foreign Key(USER)

-page Foreign Key(PAGE)

-comment

-date

FAVOURITES_PAGES

-id (Primary Key)

-user Foreign Key(USER)

-favourite_page Foreign Key(PAGE)

-date

but now one of the most important page of website is observatory, when everyone can observe activity others users. So I need select all pages, comments and favourites pages some users and display it in one list, sorted by date.

For better perfomance (I think) I changed my structure to this:

table USER and PROFILE without changes

ACTIVITY (additional table- have common fields: user,date) 
-id (Primary Key)
-user Foreign Key(USER)
-date
-page Foreign Key(PAGE)
-comment Foreign Key(COMMENTS)
-favourite_page Foreign Key(FAVOURITES_PAGES)


PAGES
-id (Primary Key)
-page

COMMENTS
-id (Primary Key)
-page Foreign Key(PAGE)
-comment

FAVOURITES_PAGES
-id (Primary Key)
-favourite_page Foreign Key(PAGE)

So now it is very easy get sorted records from all tables. But I have no only foreign key to PAGES, COMMENTS and FAVOURITES_PAGES in ACTIVITY table - there is about ten Foreign Key fields and in one record only one have value, others have None:

ACTIVITY
id    user     date page comment ...
1    2 2010-02-23 None     1
2    1 2010-02-21 1         None
....

It is corect solution. When I display about 40 records in one page (pagination) I must wait about one secound, but database is almost empty (a few users and about 100 records in others tables). It is depends on amount records per page - I have checked it, but why it takes too long time, becouse of relationships?

The website is built in Python/Django.

Any advices/opinion?

Answer 1

What are you using for your database? If you are using Oracle then you can monitor query performance with Grid to find out why they are running slowly. I imagine that other databases have similar query profiling capabilities and tools if you look for them.

Also -- what is your indexing scheme? Are fields that you are using as foreign keys indexed or is your database running a full table scan each time you do a join?

Answer 2

website is built in Python/Django.

When troubleshooting performance, use the "divide and conquer" approach. You're talking about your database structure, yet you mention python/django. That tells me that you really aren't sure if it's a database problem or an application problem, or a web server problem.

I think you and I both assume it's database. So run a test to see if the database performance is slow, when Python/Django are not a factor.

Using the same queries that your web app would use, run them directly in your database admin tool (PHPMyAdmin, etc.). When you execute the query using PHPMyAdmin, does it come back with your 40 rows in about a second? If so, then you know that your database design or query is slow. If it comes back immediately, then it's something to do with the application or the way that the application accesses the database.