CODEWITHSUNDEEP
mysql
MultiDev
MultiDev

Reputation: 10649

Is it possible to combine these 4 queries into one more efficient query?

I am hoping I can perform this with one single query to the database:

// These four variables can change...
$weave = 10;
$grade = 4;
$model = 1;
$brim = 7;

Now I have these 4 queries:

SELECT WeaveDesc FROM store_item_weaves WHERE ID = '$weave' LIMIT 1
SELECT GradeDesc FROM store_item_grades WHERE ID = '$grade' LIMIT 1
SELECT ModelDesc FROM store_item_models WHERE ID = '$model' LIMIT 1
SELECT BrimDesc FROM store_item_brims WHERE ID = '$brim' LIMIT 1

Is it possible to join all of these into one more efficient query?

Upvotes: 0

Views: 47

Answers (3)

bumpy
bumpy

Reputation: 2002

Try this one:

SELECT
  (SELECT WeaveDesc FROM store_item_weaves WHERE ID = '$weave' LIMIT 1) AS WeaveDesc,
  (SELECT GradeDesc FROM store_item_grades WHERE ID = '$grade' LIMIT 1) AS GradeDesc,
  (SELECT ModelDesc FROM store_item_models WHERE ID = '$model' LIMIT 1) AS ModelDesc,
  (SELECT BrimDesc FROM store_item_brims WHERE ID = '$brim' LIMIT 1) AS BrimDesc

Upvotes: 1

axiac
axiac

Reputation: 72177

If all the queries return the same number of fields and the corresponding fields have similar types and meaning then you can combine the four queries into a single query using UNION:

(SELECT 'weave' AS item, WeaveDesc AS desc FROM store_item_weaves WHERE ID = '$weave' LIMIT 1)
UNION
(SELECT 'grade' AS item, GradeDesc FROM store_item_grades WHERE ID = '$grade' LIMIT 1)
UNION
(SELECT 'model' AS item, ModelDesc FROM store_item_models WHERE ID = '$model' LIMIT 1)
UNION
(SELECT 'brim' AS item, BrimDesc FROM store_item_brims WHERE ID = '$brim' LIMIT 1)

The query retrieves at most 4 rows, at most 1 from each table. Each row has the columns item (use it to know from what table each row was extracted) and desc (the actual value extracted from the table).

It doesn't necessarily run faster that the four individual queries. It only saves the time needed to send three more requests and receive three more result sets.

Upvotes: 0

James Inglis
James Inglis

Reputation: 56

You could achieve this with a union query, like the following:

(SELECT WeaveDesc FROM store_item_weaves WHERE ID = '$weave' LIMIT 1)
UNION
(SELECT GradeDesc FROM store_item_grades WHERE ID = '$grade' LIMIT 1)
UNION
(SELECT ModelDesc FROM store_item_models WHERE ID = '$model' LIMIT 1)
UNION
(SELECT BrimDesc FROM store_item_brims WHERE ID = '$brim' LIMIT 1)

The database performance is not going to be significantly faster than running the queries individually, and may in fact be more difficult to manipulate the result set without adding an extra column to your result set.

It also goes without saying that you should follow the best practice of your programming language to use parameterized queries to prevent SQL injection.

Upvotes: 1

Related Questions