up vote 1 down vote favorite

I'm trying to query 2 tables where the first table will return 1 row and the second table will return multiple rows. So basically the first table with return text on a page and the second table will return a list that will go within the page. Both tables have a reference row which is what both tables are queried on. (See Below)

SELECT shop_rigs.*, shop_rigs_images.*, shop_rigs_parts.*
    FROM shop_rigs
    LEFT JOIN shop_rigs_images 
        ON shop_rigs.shoprigs_ref = shop_rigs_images.shoprigsimg_ref 
    LEFT JOIN shop_rigs_parts
        ON shop_rigs.shoprigs_ref = shop_rigs_parts.shoprigsparts_ref 
    WHERE shoprigs_enabled='1' AND shoprigs_ref='$rig_select'
    ORDER BY shoprigs_order ASC

Is it better to just do 2 queries?

Thanks,

  • dane
share|improve this question

3 Answers

up vote 1 down vote accepted

I would do this in two queries. The problem isn't efficiency or the size of the respective tables, the problem is that you're create a Cartesian product between shop_rigs_images and shop_rigs_parts.

Meaning that if a given row of shop_rigs has three images and four parts, you'll get back 3x4 = 12 rows for that single shop_rig.

So here's how I'd write it:

SELECT ...
FROM shop_rigs
INNER JOIN shop_rigs_images 
    ON shop_rigs.shoprigs_ref = shop_rigs_images.shoprigsimg_ref 
WHERE shoprigs_enabled='1' AND shoprigs_ref='$rig_select'
ORDER BY shoprigs_order ASC

SELECT ...
FROM shop_rigs
INNER JOIN shop_rigs_parts
    ON shop_rigs.shoprigs_ref = shop_rigs_parts.shoprigsparts_ref 
WHERE shoprigs_enabled='1' AND shoprigs_ref='$rig_select'
ORDER BY shoprigs_order ASC

I left the select-list of columns out, because I agree with @Doug Kress that you should select only the columns you need from a given query, not all columns with *.

share|improve this answer
Thanks Bill, Now say if shop_rigs_parts is another table that returns a list. Is 3 queries too much? – teamdane Aug 19 '11 at 21:34
Cartesian products will grow geometrically with respect to the number of tables you have. It's better to do multiple individual queries with smaller result sets, than a single query that returns hundreds or thousands of times more rows than you need. – Bill Karwin Aug 19 '11 at 21:36
Sounds good, Thank you guys! – teamdane Aug 19 '11 at 21:41
up vote 0 down vote

If you're pulling a large amount of data from the first table, then it would be better to do two queries.

Also, for efficiency, it would be better to specify each column that you actually need, instead of all columns - that way, less data will be fetched and retrieved.

share|improve this answer
Thanks Doug! It's not a ton of data, would it still be more efficient to do it in 2 queries? – teamdane Aug 19 '11 at 20:56
It partially depends on how you'll use the data, and whether, in code, it actually ends up being two queries, or 1+n queries, but most of the time, if you're not transferring a ton of duplicate data, one query is perfectly fine. – Doug Kress Aug 19 '11 at 21:04
up vote 0 down vote

Joins are usually more efficient than running 2 queries, as long as you are joining on indexes, but then it depends on your data and indexes. You may want to run a "explain SELECT ....." for both options and compare "possible keys" and "rows" from your results.

share|improve this answer
I think you're talking about EXPLAIN, not DESCRIBE. DESCRIBE tells you the table columns and keys. EXPLAIN reports a query optimization plan. – Bill Karwin Aug 19 '11 at 21:16
My mistake, fixed now. – user895553 Aug 19 '11 at 21:25
I guess I'm confused on how to get the Data into an array that I can loop through for the pages text and the list? – teamdane Aug 19 '11 at 21:26

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.