Depending on the situation one can run into unexpected occurrences reading the information schema because of permissions. In short for a general user you cannot access all table meta data from that schema without proper rights... you are restricted to only some of it otherwise you get NULLs all over the place. Then again there is the significantly additional overhead reading from the info schema, but again this can be argued away in dozens of ways. Doing a simple select is the least painful of course unless the table exists and has a gazillion rows :) so running a query like SELECT 1 FROM table LIMIT 0,1 becomes the least expensive option to verify existence of a table in MySQL. ----- Original Message ----- | From: "Adam Nelson" <adam@varud.com> | To: "Skunkworks Mailing List" <skunkworks@lists.my.co.ke> | Sent: Miércoles, 23 de Octubre 2013 14:08:36 | Subject: Re: [Skunkworks] MySQL question: create table if not exists | Nonetheless, if you want to know if a table exists, the proper place | to look is in the information_schema database or if for some reason | you can't look there, run `show tables;` from within the database | you're checking. | There's no need to touch the table in order to evaluate its | existence. | -- | Kili.io - OpenStack for Africa: kili.io | Musings: twitter.com/varud | About Adam: www.linkedin.com/in/adamcnelson | On Wed, Oct 23, 2013 at 4:01 PM, Steve Obbayi < steve@sobbayi.com > | wrote: | | Yes really @Rad its the resource that returns NULL and not the | | table | | row, don't mix the two. By the way 'id' is the name of any column | | in | | the table and not 'id' persay so in essence.. | | | SELECT any_row from table; | | | should return the rows or 0 rows if the table exists. By calling | | the | | affected_rows function in the mysql api one can clarify that. NULL | | is returned when mysql doesnt find the table to select from. | | | | From: "Rad!" < conradakunga@gmail.com > | | | | | | To: "Skunkworks Mailing List" < skunkworks@lists.my.co.ke > | | | | | | Sent: Miércoles, 23 de Octubre 2013 12:19:37 | | | | | | Subject: Re: [Skunkworks] MySQL question: create table if not | | | exists | | | | | | @steve really? What if the value for ID is actually null? | | | | | | On Wed, Oct 23, 2013 at 1:32 PM, Steve Obbayi < steve@sobbayi.com | | | > | | | wrote: | | | | | | | Yes it starts with the SELECT then executes the CREATE... IF | | | | NOT | | | | EXISTS is simply meant to suppress errors that the table | | | | already | | | | exists and from an execution stand point it is no different | | | | than | | | | if | | | | it was left out. In short CREATE always checks if the table | | | | exists | | | | before creating it | | | | | | | | | | In addition to your solution you can also run a simple query | | | | directly | | | | on the table (SELECT Id FROM table ) and if it returns NULL | | | | then | | | | the | | | | table doesn't exist so you can proceed and create it | | | | | | | | | | | From: "Peter Karunyu" < pkarunyu@gmail.com > | | | | | | | | | | | | | | | To: "Skunkworks forum" < skunkworks@lists.my.co.ke > | | | | | | | | | | | | | | | Sent: Miércoles, 23 de Octubre 2013 10:38:07 | | | | | | | | | | | | | | | Subject: [Skunkworks] MySQL question: create table if not | | | | | exists | | | | | | | | | | | | | | | Dear MySQL gurus, | | | | | | | | | | | | | | | So I have this query: | | | | | | | | | | | | | | | CREATE TABLE IF NOT EXISTS my_table_a ( SELECT * FROM | | | | | my_table_b | | | | | WHERE some_column=some_value); | | | | | | | | | | | | | | | My question is, how does MySQL evaluate it? Does it start | | | | | with | | | | | the | | | | | SELECT * then goes to check if that table exists, then | | | | | creates | | | | | the | | | | | table? | | | | | | | | | | | | | | | The reason I am asking is because the SELECT * FROM | | | | | my_table_b | | | | | WHERE | | | | | some_column=some_value part is particularly nasty and takes | | | | | forever, | | | | | and therefore, I was thinking of splitting it into two thus: | | | | | | | | | | | | | | | 1. $x = SELECT COUNT(1) FROM information_schema.tables WHERE | | | | | table_schema='my_db' AND table_name='my_table_a' | | | | | | | | | | | | | | | 2. if ( ! $x ) { CREATE TABLE my_table_a ( SELECT * FROM | | | | | my_table_b | | | | | WHERE some_column=some_value); } | | | | | | | | | | | | | | | And therefore, possibly save a few seconds. | | | | | | | | | | | | | | | Or am I searching for speed in the wrong place? | | | | | | | | | | | | | | | _______________________________________________ | | | | | | | | | | | | | | | skunkworks mailing list | | | | | | | | | | | | | | | skunkworks@lists.my.co.ke | | | | | | | | | | | | | | | ------------ | | | | | | | | | | | | | | | List info, subscribe/unsubscribe | | | | | | | | | | | | | | | http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks | | | | | | | | | | | | | | | ------------ | | | | | | | | | | | | | | | Skunkworks Rules | | | | | | | | | | | | | | | http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 | | | | | | | | | | | | | | | ------------ | | | | | | | | | | | | | | | Other services @ http://my.co.ke | | | | | | | | | | | | | | _______________________________________________ | | | | | | | | | | skunkworks mailing list | | | | | | | | | | skunkworks@lists.my.co.ke | | | | | | | | | | ------------ | | | | | | | | | | List info, subscribe/unsubscribe | | | | | | | | | | http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks | | | | | | | | | | ------------ | | | | | | | | | | Skunkworks Rules | | | | | | | | | | http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 | | | | | | | | | | ------------ | | | | | | | | | | Other services @ http://my.co...ke | | | | | | | | | _______________________________________________ | | | | | | skunkworks mailing list | | | | | | skunkworks@lists.my.co.ke | | | | | | ------------ | | | | | | List info, subscribe/unsubscribe | | | | | | http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks | | | | | | ------------ | | | | | | Skunkworks Rules | | | | | | http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 | | | | | | ------------ | | | | | | Other services @ http://my.co.ke | | | | | _______________________________________________ | | | skunkworks mailing list | | | skunkworks@lists.my.co.ke | | | ------------ | | | List info, subscribe/unsubscribe | | | http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks | | | ------------ | | | Skunkworks Rules | | | http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 | | | ------------ | | | Other services @ http://my.co..ke | | _______________________________________________ | skunkworks mailing list | skunkworks@lists.my.co.ke | ------------ | List info, subscribe/unsubscribe | http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks | ------------ | Skunkworks Rules | http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 | ------------ | Other services @ http://my.co.ke