MySQL indexing tutorials

[Anon]

Hi,

I'm a MySQL newbie. After reading a couple tutorials online I decided to create my first database. Everything went fine, and it was fast, until I did left joined two other tables. This created 70 mb of tempfiles on the server, and my host wasn't happy to say the least.

Why don't they mention in the tutorials that it is very important to index tables ? Can I find any good tutorials on this?

Cheers, Aaron

[katzkek]

Check the mysql docs on left joins... they'll specify how left joins use indexing. Why was your host dissapointed about just 70 megs of temp files? That's nothing...

[[deleted]]

70MB of tempfiles is not a lot. A good server has at least a gigabyte of tempspace nowadays.

But are you sure your JOIN was correct?

it is very 'easy' to do a JOIN wrong, and get a so-called cartesian product, where the entire 'right' table gets returned once for every record on the 'left' table.
If you have 5000 records in the left and 10000 in the right table, you will get 5000*10000=50Million records, which could easily account for the 70MB of tempfiles.

And that has nothing at all to do with indexing. Indexing onle speeds up the searches because the database nolonger has to do sequencial scands accross the tables.