|Long character string in a WHERE clause filter? Performance issue? [message #90579]
||Tue, 20 April 2004 04:39
Registered: January 2004
I was wondering if theres is some performance issues/problems or considerations when running this query:
SELECT ... FROM A
WHERE column_x = 'AEBDECEZ....AEC' ???
where 'AEBDECEZ....AEC' is a 150 character string, for exemple.
'AEBDECEZ....AEC' is a generated code corresponding to different choices made by the front-end user! Each different choices registered based on a questionary (A list of questions with different answers options) will generate a different string code!
I was also thinking to index the column_x to make the query faster!
My "problem" or model is this:
I have to modelize a Form containg x questions. For each questions I'll have several options (answers) possible. Some of the questions will accept just one answer others will accept one or more.
Each end-user (through his browser) will mark his own options based upon his choice or opinion.
My database will contain different forms as I will have different customers that want to send their own "questionary (form)" to their own customers (Front-end users).
My customer should then have some statistics generated on these choices. So, instead of generating a complicated and non-optimazed query based on several AND and OR clause in the WHERE clause for each combination of the options (WHERE optionA_from_question1 = 1 AND optionB_f_q2 = 1 etc... and make the same query for each possible combination ... ), I would like to generate a unique code based on each possible combination to have something like this when runing stat:
SELECT COUNT(*) FROM ... WHERE col_code = 'unique_code_string'
--[>] count the numbers of front-end user that make this special combination of options choice!
AND my flied containing these 'unique_code_string' will be indexed to speed up retreival.
.If, for exempel, I will have 1000 users, I will then have 1000 or less unique code generated (1000 if they have each one made a different choice) and I will have 1000 rows (one row per front-end user that has filled his form).
.If it's a one day campaign: I will assume that the database activity and the volume of request depends on how many front-end user the customer will have. At this point this may not be an issue as they are each filling up their respective forms and the web browser or the java-apache-web-server will generate a corresponding unique code for this specific combination of options choosed by the front-end user. It will be only at the end of the campaign that the customer will run statistics on several combination of options choosen by his front-end users (may be his own customers).
.An index is a tradeoff of space v speed: I won't have problem of space on my disks.
Is this system/concept a good practice or idea? Do you think I will expreience some problems and performance problems with such long strings? Should I use numeric values even if they are contained in a character string?
Do you have another idea or concept about how can I implement this system in another fashion or optimize it?
Thank you very much for your attention and tips!