fenway

Your where clause needs to look for values in the non-left joined table, otherwise they'll be nulled out.

OK... well, the issue is likely update the index on rank since you're changing it 1.7M times! Do you use this index? Can you live without it during the update? There's a way to disable this.

Hmm... I guess you are updating 1.7M rows... best suggestion I have is to set the rank directly in the insert... saves doing the update later/at all!

Sound like a job for printf().

There are some excellent articles in the stickies on performance optimization that discuss covering indexes. Basically, MySQL picks one to use... not at all three.

Run the EXPLAIN on the equivalent selects.

Missing indexes?

You can't check b.CourseYear and b.StudentID is NULL together... if the latter is null, it's because all b table columns were nulled out... you need to put this condition into the on clause.

Try rewriting this with proper join syntax first.

From a DB perspective, you can have a record with from and until date/time values, one per block of time off. How you want to show this to the user is up to you.

Like I thought... you don't have a 3-column index, you have 3 indexes, each with one column.... that's very different.

NOW() + INTERVAL 1 MONTH saves some parents.

The way to limit the left join is the pull it out into another table with limit 1.

Agreed.... no number should ever appear "twice" in any field.

Not easily... you'll have to recompile the server. Apparently in 5.1 you can roll-your-own parser.

If you're using 5.0, you can query the information_schema table.

@Barand -- no receipt, cash only ;-)

You can simply add a modified column and store NOW() whenever it's changed. There's a TIMESTAMP magic feature too, but I would advise against it.

Could you post your SHOW CREATE TABLE? I think we're talking about different things.

Sorry, I misread the original post. a.anything will never be null in a left join... you need to check the b. table. And don't you want inner join for everything after the import table?

I just bought an engagement ring today!

Why so many left joins? You only need 2 tables and a lot of on conditions.

What's the difference? If you use an inner join, you'll necessarily get one image.

That's quite bad.... that should never happen, because (A) root has access to everything unless the permissions are messed up and (B) you're never supposed to be root.

I've never worked with such things... perhaps someone else can chime in?