
Ask HN: Question about MySQL "create if not exists" performance. - NathanKP
After searching Google fairly extensively I haven't been able to find any analysis of the performance of "create if not exists" MySQL statements.<p>I want to ensure that a MySQL table is existent before using it.  The question is whether or not it will be a major performance hit to do a simple "create if not exists" once each time before using  a table which may or may not exist.  Alternately I could query the information_schema manually to see if the table exists and if not I can create it.<p>My gut feeling is that it is more efficient to simply run "create if not exists" before using a table which may or may not exist.  This way I only have one MySQL statement that can simply be executed before using the table.<p>Does anyone else have any thoughts about the comparative benefits or performance issues associated with doing a "create if not exists" on each execution?
======
duskwuff
This really shouldn't be necessary at all. In a well-designed application, the
table schema should be completely known to the application at all times -- the
existence of a table should never be in doubt. If for some reason this isn't
the case, you'll be much better served by catching "table doesn't exist"
errors when they occur rather than preemptively trying to create tables which
already exist.

~~~
NathanKP
I considered this aspect but I'm using these tables for statistics. Basically
I am giving each user of my website a table for storing statistics about their
site usage. Some users however, never really "complete" the process and don't
really use the website, thus they have no need for statistic tables. So I have
the idea of saving space by creating statistic tables as they are needed.

I'm going to try the "create if not exists" first and if that causes too much
MySQL server load I'll just create the statistic tables at user registration
and save processor usage at the expense of disk usage.

~~~
duskwuff
Don't create tables per user. Seriously, don't. There's a (relatively low)
limit to how many tables MySQL will keep in memory, and once you hit that
limit it isn't very good at keeping the right tables loaded. Instead, redesign
your database schema so that you can put all of your users' data in the same
tables.

