MySQL, do I need a JOIN if I'm only returning data from 1 table?

Forum Moderators: open

Message Too Old, No Replies

MySQL, do I need a JOIN if I'm only returning data from 1 table?

csdude55

3:05 am on Jan 27, 2016 (gmt 0)

This is my query:


SELECT tableA.id, var1, var2, var3, var4, var5
FROM tableA
LEFT JOIN tableB
ON tableA.id = tableB.id
WHERE tableB.category = 'example'
ORDER BY var3 DESC

I'm not actually returning any data from tableB, all I'm doing is checking the table to see if the category matches 'example', and if so I'm returning the matching ID and data from tableA.

Is there a better / faster option than using LEFT JOIN here?

topr8

9:56 am on Jan 27, 2016 (gmt 0)

yes you need the join as although you are not returning fields from tableB you are using it in the query.
assuming both id fields are primary keys or at least indexed it should be pretty quick.

although i would ask what is the point of tableB? if it joins to tableA in a straight forward 1 to 1 way, why aren't the fields from tableB just in tableA and then you'd only have one table.

maybe i'm missing something.

csdude55

10:11 am on Jan 27, 2016 (gmt 0)

OK, thanks for the info :-) I thought that was the case, but I'm trying to speed things up so I wanted to make sure. On the outside, speed is OK, but I'm using a lot of CPU and RAM so I'm optimizing as much as possible to buy time before upgrading hardware.

In this case, tableB has 2 columns: id and category. There can be up to 71 different categories for each ID, so it's easier to manage with them in a separate table.

LifeinAsia

3:48 pm on Jan 27, 2016 (gmt 0)

Any particular reason you're doing a LEFT JOIN instead of an INNER JOIN? You might get some performance gain by changing to an INNER.

topr8

5:15 pm on Jan 27, 2016 (gmt 0)

i understand what you are doing now.

i would say it was more usual and probably more efficient to have a slightly different structure, using table c to join multiple categories to an item in table a and table b just being a straight forward list of all possible categories:

tableA
Aid primary key integer
var
var
var
etc

table B
Bid primary key integer
Bname varchar -- the name of the category

table C
reference_to_a int
reference_to_b int

Then use a simple inner join:
SELECT tableA.id, tableA.var1, tableA.var2, tableA.var3, tableA.var4, tableA.var5
FROM tableA, tableB, tableC
WHERE tableB.bname = 'example' AND tableB.Bid= tableC.reference_to_b AND tableA.Aid = tableC.reference_to_a
ORDER BY var3 DESC

[and you'd need to INDEX the 2 fields in table c ]