I am doing a database design that stores search keywords of a search form, used to generate the most popular search keyword reports by date, month and year. I have made a simple schema:
CREATE TABLE SearchTerm (
ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
SearchTerm VARCHAR( 255 ) NOT NULL,
Date DATE NOT NULL
);
In this case, search term will be redundant in the table and make the database grow faster. I want to avoid data redundancy.
Also I am not sure if using the date data type or unix timestamp will make a difference in retrieving the data faster.
So, should I create a new table to store a date that links to master table by id? For example,
CREATE TABLE SearchTerm (
ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
SearchTerm VARCHAR( 255 ) NOT NULL,
Hit INTEGER NOT NULL,
DateID INTEGER NOT NULL
);
CREATE TABLE DateofSearchTerm (
DateID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Date DATE NOT NULL
);
My concern is performance as the database grows. If the database is not designed properly , it would be leading to performance and bottleneck issues.
One of my friends has done an analytic site, but it is not going well. I figured out that there are 3 million records in the table and his query is taking more than 3 minutes to return results.
And only one table he created, then save “Event Name”, “Start data”,” End date” something like that. When generating monthly report, it could not show the report due to timeout issues (server time out, query time out, jquery response time out).
His SQL query is too expensive. He use group by date in sp to get how many users daily and monthly. Then passes the result to populate flash based report. I fixed it to see report by increasing command time out. But I don’t think it is a good idea. Data is continuing to grow, it would be 4 mill records in end of this year. At the end of the day , increasing command time out will not work. This issue is a result of weak database design. I don’t want to follow it.
Please advise me and share your experience to avoid these issues. How should I design search term db schema?