Ok I have to develop a query according to some user input to filter the outcomes.
The query essentially goes something similar to this:
Choose * FROM my_table ORDER BY ordering_fld
You will find four text boxes by which customers can pick to filter the information, meaning I'd need to dynamically develop a "WHERE" clause in it for that first filter used after which "AND" clauses for every subsequent filter joined.
Because I am too lazy to get this done, I have just made every filter an "AND" clause and set a "WHERE 1" clause within the query automatically.
Choose * FROM my_table WHERE 1 ORDER BY ordering_fld
So my real question is, have I done something which will negatively modify the performance of my query or buggered other things up by any means I ought to be remotely concerned about?
MySQL will optimize your
I simply went this question on my small test database:
EXPLAIN EXTENDED Choose * FROM t_source WHERE 1 AND id < 100
also it provided the next
choose `test`.`t_source`.`id` AS `id`,`test`.`t_source`.`value` AS `value`,`test`.`t_source`.`val` AS `val`,`test`.`t_source`.`nid` AS `nid` from `test`.`t_source` where (`test`.`t_source`.`id` < 100)
As you can tell, no
The documentation on
WHERE clause optimisation in
MySQL mentions this:
(a<b AND b=c) Along with aEquals5 -> b>5 AND b=c Along with aEquals5
Constant condition removal (needed due to constant folding):
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) -> B=5 OR B=6
5 = 5 and
5 = 6 parts within the example above.
You are able to EXPLAIN your query:
and find out whether it does anything in a different way, that we doubt. I'd use 1=1, so it's more obvious.
You might like to add LIMIT 1000 or something like that, when no parameters are utilized and also the table will get large, will you want to come back everything?
WHERE 1 is really a constant, deterministic expression which is "enhanced out" by any decent DB engine.
If there's a great means by your selected language to prevent building SQL yourself, use that rather. I love Python and Django, and also the Django ORM causes it to be super easy to filter results according to user input.
If you're devoted to building the SQL yourself, make sure to sanitize user inputs against SQL injection, and then try to encapsulate SQL building inside a separate module out of your filter logic.
Also, query performance shouldn't be your concern until it might be an issue, so it most likely will not til you have 1000's or countless rows. So when it will come time for you to optimize, adding a couple of indexes on posts employed for WHERE and JOIN goes a lengthy way.
To enhance performance, use column indexes on fields hear "WHERE"
Standard SQL Injection Disclaimers here...
One factor you could do this, to prevent SQL injection because you know it is just four parameters is make use of a saved procedure in which you pass values for that fields or NULL. I don't know of mySQL saved proc syntax, however the query would boil lower to
Choose * FROM my_table WHERE Field1 = ISNULL(@Field1, Field1) AND Field2 = ISNULL(@Field2, Field2) ... ORDRE BY ordering_fld
We have been doing something similiar much less sometime ago and there are a couple of stuff that we observed:
- Establishing the indexes around the posts i was (possibly) blocking, enhanced performance
- The WHERE 1 part could be overlooked completely when the filters're not used. (unsure whether it is applicable for your situation) Does not really make a difference, but 'feels' right.
- SQL injection should not be forgotten
Also, should you 'only' have 4 filters, you can increase your saved procedure and pass in null values and appearance on their behalf. (much like n8wrl recommended meanwhile)