How to improve an IF statement in MySQL?


Problem :

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?



Solution :

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 items and 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.


Mysql Tutorials

Mysql Howto..

How to Create a Dynamic Drop Down List in PHP populated from MySQL Database

How do I set mysql password?

PHP: How to store HTML as it is in MySQL?

How should I store weekly poll data?

How do you package the JDBC jar in an extension?

How to add json data in mysql database

How to update a row in mysql and insert new record?

How to give different values to number of limit people in mysql database?

Django - database data not showing in application?

how to fetch data from two tables in mysql?

How to prevent MYSQL GROUP BY from collapsing NULL values into a single row

How to find and show number of similar words in two different column within same table(MySql,PHP)

How a probably NULL-valued attribute could still reference another attribute in MySQL

sql show all dates on count query

How to maintain continuos auto_increment values in MySQL

How to design MySQL Database

How to take dump of serverdb in mysql

How put apps in a mysql database and display them [closed]

How to get all category with their sum of products using their attribute in mysql

How to get column specifications from MySQL with Java? [closed]

MySQL Performance - How to accelerate query for Build-Up / Burn-Down Chart?

How to change the text after extract it in PHP?

How to see log files in MySQL?

How to create a union of two tables with null values?

ERROR 1048 (23000) Column cannot be NULL, however I am inserting valid data

How to connect to mysql using java?

How to change (format) date output in mysql?

How to create a MySQL log using Java

How do I convert a basic static site to dynamic so that I can give it a CMS? PHP/MySQL [closed]

How to insert data when we have Relationship constraints in MySQL database