This is my first time delving in to the use of JOIN, so please bear with me if this is a stupid question.

I'm splitting a table into two tables ('classifieds' and 'classifieds_type') to eliminate rows that are almost duplicate but for one column (column 'type'). When it was one table, here is a standard query:

SELECT id, username, price, expiration, title, image, description
FROM classifieds
WHERE type = 'pontiac'
AND category = 'Autos'
AND subcat = 'Parts and Accessories'
AND expiration >= 20120130
AND (
status = ''
OR status IS NULL
)
ORDER BY id DESC

On average, this query would take 0.0016 sec to complete.

Now, with two tables, to get the same information I'm using this query:

SELECT classifieds_type.id, username, price, classifieds.expiration, title, image, description
FROM classifieds
LEFT JOIN classifieds_type ON classifieds_type.id = classifieds.id
WHERE classifieds_type.type= 'pontiac'
AND category = 'Autos'
AND subcat = 'Parts and Accessories'
AND classifieds.expiration >= 20120130
AND
(
status = ''
OR status IS NULL
)
ORDER BY id DESC

This query is now taking 0.0121 sec to complete. Which isn't bad, I guess, but it's more than 7 times slower than the first.

On table 'classifieds_type', I have an index on 'type' and 'expiration'. And on table 'classifieds', I have an index on 'category', 'subcat', and 'status'.

EXPLAIN does verify that the two indexes are being used.

(Note, I had read that using IS NULL would slow things down, so I removed it for testing, but it had no impact.)

Can you guys recommend a way to optimize the second query to make it faster? Or is it normal for a JOIN like this to be a little slower by default?

On Mon, 30 Jan 2012 02:08:44 -0800 (PST), Jason C wrote:

Oh boy. Are you sure you want LEFT JOIN there? Do you REALLY want data
coming back if you have no 'pontiac' adverts in the system that meet the
rest of the criteria? Normal INNER JOIN is probably what the rest of
what you're doing actually wants.

What? Are the users losing interest and wandering away because it's
taking too long?

You will want indexes on the join fields, and secondarily on the fields
you're selecting for, if you know that in advance. You'll always need to
balance lookup speed (many indexes) with insertion/update speed (few
indexes) for what your task is. In this case, you're looking up a lot
more than you're inserting, so lots of indexes is good and indexing
every column separately wouldn't be as bad as choosing the wrong join.

It's faster than testing for an actual value. Usually by a lot.

Is it running too slowly now? I really recommend not spending the time
optimizing things before you've got a problem, especially on the
database end where good general practice will typically take you to
"good enough" and probably all the way to "the database is not your
chokepoint" without any further consideration. The join selection
problem isn't even one of performance; that's a "gotcha" that's waiting
for other reasons than being slow.

--
The only thing I'd use on guinea-fowl is a shredder. Same with
peacocks. The sound of peacocks being shredded can't possibly be any
worse than the sound of peacocks not being shredded.
-- Tanuki

On Monday, January 30, 2012 8:44:41 AM UTC-5, Peter H. Coffin wrote:
If I understand the differences correctly, INNER JOIN will only return rowswhen there is a match, while LEFT JOIN will always return all rows, right?

In this case, I chose LEFT JOIN because both tables are always inserted at the same time, and there should never be an instance where the two do not have matching rows.

As far as speed goes, I changed the query to INNER JOIN, but the query speed was identical (0.0121).


No, I haven't put this query into production yet. My site has about 4.5 million monthly pageviews, though, so I don't know if the increased delay in response will become noticeable once I put it in to production.

A couple of years ago, when I first started moving data to MySQL, I had major problems with the database response time causing Apache to timeout. So, while it's true that I AM pre-optimizing, it's because I can't afford the loss of doing it wrong the first time and losing traffic because of slow response times.

I'll be putting it in to production tonight, though, so I'll find out for sure then! :-)

On Jan 30, 9:16*pm, Jason C <jwcarl... (AT) gmail (DOT) com> wrote:

Sounds like the man's man way of doing things...
I don't always test my code, but when I do, it is in
production...

On Friday, February 3, 2012 8:36:08 PM UTC-5, onedbguru wrote:
LOL I know we just met, but you know me too well already! :-D