May 21, 2010 at 3:06 am
hi guys, i just want to ask for your advice/opinion/suggestion.
i collect search words typed by the visitors to my website. i have this inserted in a sql server database. my main purpose for this was/is to have some sort of google-like suggest. if the search words found anything on my database, it's flagged as a successful keyword and will be shown in the auto-suggest; if not, it's flagged as unsuccessful keyword and will not be shown in the auto-suggest. i also made use of these keywords(un/successful) into a tag-cloud sort of thing to gather the thoughts of my visitors to gauge how they search things.
now i have several hundred thousands of rows of these collected keywords and i somewhat notice a lag/delay when i type into the search text field before the suggested words appear.
it's just a simple table, something like below and just a simple sql query:
id-----+keywords--+successful
1------+dog-------+ticked
2------+dog-------+ticked
3------+dog-------+ticked
4------+bird-------+unticked
5------+bird-------+unticked
6------+cat-------+ticked
7------+fish-------+ticked
8------+fish-------+ticked
.
.
.
what can you advice? thanks for any suggestions?
May 21, 2010 at 5:44 am
Do you have a unique constraint on that column / columns? No need to have an ID field. The unique constraint will handle that, so that you have only one distinct record. No need to have duplicates, that only wastes space and time for what you want.
Then you can have an index on the other column, if you do not include it in the unique constraint. That will speed up the query.
Andrew SQLDBA
May 21, 2010 at 5:50 am
I just noticed that the column name of "Successful", that should be a bit data type, not text. A database will have better performance using a numeric value for that column, instead of having to compare text a value.
You can also completely for go this method, and query thru the actual data table as the person is typing the word. If you are using SQL FullText Search, this will be simple.
Andrew SQLDBA
May 21, 2010 at 6:48 am
hi andrew thanks for your reply.
i don't have unique constraint on that table, just the id field.
and my apologies, i was not able to clarify the 'successful' column. it's a bit data type. it's either ticked or unticked checkbox
May 21, 2010 at 7:58 am
Ok, in the database, that column will store either a 1 or a 0
Query the table, not open the table, and you will see them
Andrew SQLDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply