6 ms·
Ask HN: Question about MySQL "create if not exists" performance.
After searching Google fairly extensively I haven't been able to find any analysis of the performance of "create if not exists" MySQL statements.
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.
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.
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 17y agoThis 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 17y agoI 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 17y agoDon'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.