How to improve an IF statement in MySQL?
Consider the mysql
SELECT If( (SELECT `price` FROM `prices` AS `B` WHERE `A`.`Item_ID` = `B`.`Item_ID`)<5, 5, (SELECT `price` FROM `prices` AS `B` WHERE `A`.`Item_ID` = `B`.`Item_ID`) ) AS `Item_Price' FROM `items` AS `A`
where it used to find the price from
prices table for each item from the
items table, and If the price is less than 5, then it is to 5. The problem with this is that if the
IF statement comes false, i.e. the price greater or equal to 5, then it runs another search for the same price which may slow down the page. Is there a way to improve this?
You can use a simple
LEFT JOIN instead of a sub-query:
SELECT If(COALESCE(`price`, 0) < 5, 5, `price`) AS `Item_Price' FROM `items` AS `A` LEFT JOIN `prices` AS `B` ON `A`.`Item_ID` = `B`.`Item_ID`
This assumes that there is a one-to-one relationship between
prices based on
Item_ID. If your initial query runs without problems and your sub-query returns just a scalar value, so such a one-to-one relationship indeed exists.
- A Linux, Apache, MySQL, PHP (LAMP) stack is a combination of open source software that is typically installed together to enable a server to host dynamic […]
- MySQL is an the most popular open-source database management system, in most of hosting company its has been bundle by LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack. Another alternative […]
- MySQL is the world’s most popular open-source database. Despite its powerful features, MySQL is simple to set up and easy to use. Below are some instructions […]