The latest version of High Performance MySQL (3rd ed.) recommends that one:


  ...place an upper limit on the tablespace's size to keep it from growing
  very large, because once it grows, it doesn't shrink.


They give the example of:

...ibdata3:1G:autoextend:max:2G


But it isn't clear to me what happens if your dataset keeps growing and hits this maximum. Unfortunately the MySQL docs aren't much help either.

Can someone shed some light on this? It seems like this advice is kind of dangerous on the face of it.

Thanks!

The reason you put the WHERE 1=2 clause in that SELECT INTO query is to create a field-copy of the existing table with no data.

If you did this:

select *
into Table2
from Table1


Table2 would be an exact duplicate of Table1, including the data rows.  But if you don't want the data contained in Table1, and you just want the table structure, you put a WHERE clause to filter out all of the data.

BOL SELECT INTO Reference quote:


  SELECT…INTO creates a new table in the default filegroup and inserts the resulting rows from the query into it.


If your WHERE clause has no resulting rows, none will be inserted into the new table:  Therefore, you end up with the duplicate schema of the original table with no data (which would be the desired result in this case).

The same effect can be achieved with TOP (0), for example:

select top (0) *
into Table2
from Table1;


Note:  The SELECT INTO will not duplicate the source table's indexes, constraints, triggers, or partition schema.
