Question: I'm working on an API written in NodeJS and connecting to a MySQL 5.5 database. On our live system, we have one query that's running at over 7 seconds. We've traced this down to a couple of tables having over 5 millions records in and incorrectly indexed.

On our dev areas, we don't have that number of records. I think I've identified the query that's causing the problem but, short of putting 5 millions records on my laptop (which will take ages to generate), I cannot prove it.

Is there a setting/technique that I can use to emulate having millions of database records without actually having the records in?

Answer: 
My original thought was of get a lot of data into the databases to highlight the issue. I managed to get a million records in, just by leaving a script hammering the (dev) API overnight on.

The EXPLAIN function was pretty helpful. I also found the EXPLAIN EXTENDED one pretty useful too. However, neither of these highlighted the problem as it wasn't an indexing issue.

I also found the "RESET QUERY CACHE" function useful, as this cleared down the cached data for debugging purposes.

Some numpty had put a "WHERE DATE\_FORMAT() = ''" in there. Although quite annoyed at how long it took me to notice it (it was a Friday afternoon - be kind), I found and fixed the issue. Thanks all.

The moral of the story is: **NEVER STICK FUNCTIONS IN WHERE CLAUSES!!!**
In Oracle, you can load in table statistics to from other databases - ie production to dev - in effect, mock the data volumes used when doing things like explain plans. Might be worth seeing if something equivalent exists under MySQL. It's not perfect, but would give you a better idea of what's going on under the hood. 

Sorry I can't be more specific, I've just woken up and I haven't had tea yet.