3

Below is a gross over simplification of 2 very large tables I'm working worth.

campaign table

| id | uid | name | contact | pin | icon |
| 1  | 7   | bob  | ted     | y6w | yuy  |
| 2  | 7   | ned  | joe     | y6e | ygy  |
| 3  | 6   | sam  | jon     | y6t | ouy  |

records table

| id | uid | cid | fname | lname | address | city | phone |
| 1  | 7   | 1   | lars  | jack  | 13 main | lkjh | 55555 |
| 2  | 7   | 1   | rars  | jock  | 10 maun | oyjh | 55595 |
| 2  | 7   | 1   | ssrs  | frck  | 10 eaun | oyrh | 88595 |

The page loops thru the records table and prints the results to an HTML table. The existing code, for some reason, does a separate query for each record "select name from campaign where id = $res['cid']" I'd like to get rid of the second query and do a some kind of join but what is the most effective way to do it?

I need to

SELECT * FROM records

and also 

SELECT name FROM campaigns WHERE campaigns.id = records.cid

in a single query.

How can I do this efficiently?

peterm
  • 91,357
  • 15
  • 148
  • 157
I wrestled a bear once.
  • 22,983
  • 19
  • 69
  • 116

4 Answers4

2

Simply join the two tables. You already have the required WHERE condition. Select all columns from one but only one column from the other. Like this:

SELECT records.*, campaigns.name
FROM records, campaigns
WHERE campaigns.id = records.cid

Note that a record row without matching campaign will get lost. To avoid that, rephrase your query like this:

SELECT records.*, campaigns.name
FROM records LEFT JOIN campaigns
ON campaigns.id = records.cid

Now you'll get NULL names instead of missing rows.

MvG
  • 57,380
  • 22
  • 148
  • 276
2

The "most efficient" part is where the answer becomes very tricky. Generally a great way to do this would be to simply write a query with a join on the two tables and happily skip away singing songs about kittens. However, it really depends on a lot more factors. how big are the tables, are they indexed nicely on the right columns for the query? When the query runs, how many records are generated? Are the results being ordered in the query?

This is where is starts being a little bit of an art over science. Have a look at the explain plan, understand what is happening, look for ways to make it more efficient or simpler. Sometimes running two subqueries in the from clause that will generate only a subset of data each is much more efficient than trying to join the entire tables and select data you need from there.

To answer this question in more detail, while hoping to be accurate for your particular case will need a LOT more information.

If I was to guess at some of these things in your database, I would suggest the following using a simple join if your tables are less than a few million rows and your database performance is decent. If you are re-running the EXACT query multiple times, even a slow query can be cached by MySQL VERY nicely, so look at that as well. I have an application running on a terribly specc'ed machine, where I wrote a cron job that simply runs a few queries with new data that is loaded overnight and all my users think the queries are instant as I make sure that they are cached. Sometimes it is the little tricks that really pay off.

Lastly, if you are actually just starting out with SQL or aren't as familiar as you think you might eventually get - you might want to read this Q&A that I wrote which covers off a lot of basic to intermediate topcs on queries, such as joins, subqueries, aggregate queries and basically a lot more stuff that is worth knowing.

Community
  • 1
  • 1
Fluffeh
  • 33,228
  • 16
  • 67
  • 80
  • a very valid answer, thank you, but not very helpful – I wrestled a bear once. Oct 02 '13 at 01:30
  • @Adelphia It is very difficult to give you a thorough and accurate answer based on the question you asked unfortunately. Efficiency and speed in queries is often a matter of getting a few ideas together, running them all a bunch of times and seeing what works the best. Very rarely can you simply look at a query and say "It will be most efficient if you...." especially if you can't look into numerous details in the database. – Fluffeh Oct 02 '13 at 01:33
1

You can use this query

SELECT records.*, campaigns.name
FROM records, campaigns
WHERE campaigns.id = records.cid

But, it's much better to use INNER JOIN (the new ANSI standard, ANSI-92) because it's more readable and you can easily replace INNER with LEFT or other types of join.

SELECT records.*, campaigns.name
FROM records INNER JOIN campaigns
ON campaigns.id = records.cid

More explanation here:

  1. SQL Inner Join. ON condition vs WHERE clause
  2. INNER JOIN ON vs WHERE clause
Community
  • 1
  • 1
Rully Hendrawan
  • 156
  • 4
0
SELECT *
FROM records
LEFT JOIN campaigns
on records.cid = campaigns.id;

Using a left join instead of inner join guarantees that you will still list every records entry.

Fluffeh
  • 33,228
  • 16
  • 67
  • 80
AgRizzo
  • 5,261
  • 1
  • 13
  • 28
  • Left join goes with `ON`, not `WHERE`. And you are selecting *all* columns from `campaigns`, which is more than OP requested. Since that might turn an index lookup into a full table lookup, requesting too much data might have performance drawbacks. – MvG Oct 02 '13 at 01:27
  • Answering my comment to your post on my post might be somewhat confusing. Let's try to keep the comments here… Your original answer used the keyword `WHERE` to denote the join condition, but Fluffeh already edited your answer, seconds after I posted my comment, so that part of my comment is obsolete by now. – MvG Oct 02 '13 at 01:43