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
Peter, I doubt you really need to split the table. Try indexing my_table_b.some_column before splitting the table. On Wed, Oct 23, 2013 at 2:19 PM, Rad! <conradakunga@gmail.com> wrote:
@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
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. ----- Original Message ----- | 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
participants (3)
-
David Njuguna -
Rad! -
Steve Obbayi