How to create a SQL test enviornment?

Question

Objective: I'm trying to determining a best approach for table structure and queries; namely, how best to create composite index keys to optimise a four table join with about 500k rows in each table.

The Mental Block: It's the building of the tables and filling them with test data that I am lousy at understanding (SQL rookie). This SO post describes 75% of what I'm trying to solve and I think I could finish off my understanding if I could just get the answer's code to run on my localhost (apache2, mysql5.1, phpMyAdmin).

The Request for help: Jake Feasel's sqlfiddle site is a great tool, but according to him it does not scale to that level. But, can someone use sqlfiddle to show me how I might build this environment on my local machine? I hope to extrapolate your sqlfiddle post to replicate the example code I mentioned above.

An assumption: Most of my SQL experience has been tweaking an ADOdb Database Abstraction Library, but I realise MYSQL code will differ slightly in the testing environment. Still, I assume the optimising knowledge I would gain by knowing how to setup a local SQL testing environment and the subsequent db structure and queries will be applicable and beneficial when implemented in an ADOdb environment.

Answer 1

What you really need is a data generator tool that will help you populate a database with thousands or millions of records, and after you have a bloated database with meaningful data you can start your performance tests experimenting the best relationship, index and joins that will help you detect what really needs to be optimized.

One that I personally used in the past, was: GenerateData But there are others.

Answer 2

http://sqlfiddle.com/#!2/b771b/2

Here, you can see that I've copied your table definitions and pasted them into the schema panel. I've also added some dummy data into each of them. Using that basic structure, I've executed your query as well; you can see that it produces the same execution plan that your pastie is displaying (expand the "+ View Exection Plan" on the fiddle).

You can see that it does run fine on SQL Fiddle. That addresses the question in your title ("How to create a SQL test environment"). What part of your other request "how I might build this environment on my local machine?" are you having trouble with?