I’m working on a project which is going to be an e-mail marketing application and I’m running into a problem so I’m looking for some advice here.
The application has a CRM tabel that contains all the information of subscribers:
id int(10) primary key
crmid char(20) index (A unique hash)
And more fields depending on what is added for the specific implementation of course.
Now whenever there is an email send to a CRM object I store this in a second table ‘Send Emails’ which is as follows:
id int(10), primary key
emails_id int(10) index
crmid char(20) index
opened tinyint(1) index
clicked tinyint(1) index
So I have 100.000 records in my CRM table and I have send 10 emails to all these CRM objects so I have 1.000.000 records in my ‘Send Emails’ table.
Besides that we also store page statistics and also keep track of which user click what link in what email on which data. And we need to be able to filter on this too.
The problem is that I need to be able to make selections in the application where for example I need the following:
1: I want all crm-records that have received email 1
2: I want all crm-records that have received a email (Regardless which one)
3: I want all crm-records that have opened email 1 but not have received email 2
4: I want all crm-records that have opened email 1 and not have clicked in email 2
These are small examples but there is no limit on the size of the selections. The results of these selections must be available instantly because we want to:
1: Be able to send e-mail to a specific selection
2: Generate user specific content on a webpage based on these selections.
For now I tried several options:
1: Using sub queries to filter the results
2: Using endless left joins
3: Chunking the filters and compare the data in PHP
When I create simple selections all these options either bring the CPU to a load of 100% for minutes without result or take a very long time to complete.
Anyone advice on how to tackle this, or is this basically insane? Or is the solution to just show up with massive CPU power on the database server (since now we are testing this on a development machine which can’t handle the simple queries even)?