live website creation and promotion

Tried to install and configure IBM DB2 on our virtual dedicated server. It just doesn’t work well - half of operations were aborted due to memory limit. So we’re moving to 1and1 dedicated root server (Fedora Core 4, Plesk 8, 1Gb RAM, unlimited traffic). They have 2Gb RAM option as well, but I don’t think we will really need it soon, even though DB2 is not very lightweight software

P.S. DB2 is just fantastic, I’m able to do things that MySQL can only dream of, and it still works fast, despite of the fact that I didn’t set up any indexes yet - except of primary keys in tables.

Auto-increment (as in MySQL) is called Identity in DB2 (value generation parameter). And it wasn’t too easy to find it out…

I’m having constant problems with MySQL, it is very slow now, especially when there are dozens of search bots parsing my sites.
I also found it very annoying that simple subqueries take a lot of time to execute - it works about 10 times faster after I separate them and use two queries instead of one, passing the data from first to second via PHP script.
It appears to be a well-known problem for MySQL 4.1 - I’m not sure if that’s still happening with 5.0 version - but I don’t have too much time to make thorough tests, so I’m going to move to PostgreSQL, as it seems to be a much better solution. Right now my database code is not too complex (yet), so it won’t take long.
Any comments?

P.S. I understand that this move also means ideology change - things should be done with stored procedures, foreign keys, triggers and so on - so part of business logic is moving to DB. But actually this is exactly how it should be, IMHO.

Update. I’m seriously thinking about IBM’s DB2…

I wasn’t smart enough to add caching to all counters. So it required some temporary script to add missing things - when I realized that. Now all tag global counters, tag per-member counters and program (XX users) counters are cached - they are recounted each time something of this changes. Of course recounting is only for things being changed.

This saves a lot of database time on requests, I removed all GROUP BY query parts, changing to DISTINCT when I need distinct results.

So, if it was something like:
SELECT tag, COUNT(id) AS count FROM tags_table GROUP BY tag WHERE …
Then now it’s:
SELECT DISTINCT tag, tagcount FROM tags_table WHERE …

I will probably publish database schema soon, I just want to check out that this is the final variant and I didn’t forget something else

At the same time I am developing another (social) project, and the only thing I miss right now is DB transaction capability. Unfortunately, almost every hosting now uses MySQL version 3.23, which does not support transactions. So when you’re trying to add data into several tables and suddenly have been interrupted in the middle - you should care to fix the data, not database…

I think that when those projects become more popular, and I will move to dedicated server - I will change the queries to be transactional, it should save me tons of headache later.

First of all, I know that my database design is far from perfect, but it is working right now and I will not change it until website become very popular (if at all). And even then I may not change it, instead using caching methods to lower server load.

What we need: table for users, table for programs, table for tags.
I could have made separate table for programs and table for user-program pairs, but I did it simpler and made one table for programs which contains also user who added this program. That means that program name field is not unique value and I have to use GROUP BY a lot, which is a little slow, but right now it’s working good. The same thing is for tags table - tag name is not unique there. I’ll post here if there will be any changes in database structure.

Meanwhile this is it:

Members table
id          MEDIUMINT(8) autoincrement, primary key
username    VARCHAR(25)
mail        VARCHAR(250)
password    VARCHAR(32)  = hash of password
registered  INT(10)      = time()

Programs table
id          INT(10)      autoincrement, primary key
user        MEDIUMINT(8) = members.id
program     VARCHAR(250) = program name
description VARCHAR(250)
version     VARCHAR(30)
link        VARCHAR(250)
created     INT(10)      = time()

Tags table
id          INT(10)      autoincrement, primary key
user        MEDIUMINT(8) = member.id
program     INT(10)      = program.id
tag         VARCHAR(30)  = tag name