Re: [Skunkworks] MySQL question: create table if not exists
I can't imagine that a mature framework like CodeIgniter would touch the table in order to check for the existence of that table.
Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons. So optimization that is checking schema status is a wrong approach in my humble opinion Muriithi
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord and a variety of other third party ORMs. I wrote the queries herein to make it easier to explain my situation.
On the (de)merits of using ORMs and other database abstractions, I believe thats a *discussion* for another day :-)
On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com> wrote:
@steve - you're right about the permission issues and the SELECT can be a good workaround
@peter - why are you writing SQL from a program? Presumably, whatever framework you're using has abstracted this stuff out, no?
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote:
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.
------------------------------
*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 <https://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 <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 <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 <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
-- Regards, Peter Karunyu -------------------
_______________________________________________ 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
@murithi You bring up an important point. Ideally, schema migrations are done by a different database user than the one used by the application under normal use. This isn't commonly done but is definitely best practice. Nonetheless, the schema migration has to be done via a user with rights to do it ... you can't create a table without the privileges to do so. -- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson On Wed, Oct 23, 2013 at 10:43 PM, William Muriithi < william.muriithi@gmail.com> wrote:
I can't imagine that a mature framework like CodeIgniter would touch the table in order to check for the existence of that table.
Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons.
So optimization that is checking schema status is a wrong approach in my humble opinion
Muriithi
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord and a variety of other third party ORMs. I wrote the queries herein to make it easier to explain my situation.
On the (de)merits of using ORMs and other database abstractions, I believe thats a *discussion* for another day :-)
On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com> wrote:
@steve - you're right about the permission issues and the SELECT can
be a
good workaround
@peter - why are you writing SQL from a program? Presumably, whatever framework you're using has abstracted this stuff out, no?
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote:
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.
------------------------------
*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 <https://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 <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 <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 <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
-- Regards, Peter Karunyu -------------------
_______________________________________________ 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 looking at the query seems like you are duplicating a tables structure and copying the respective record which in turn brings up the arguments by nelson (above) why not create a view? tip: select * is more hungry resource wise that select id
I think peter is trying to have data from one table to another table but in a different format. Perhaps the one table is a Key => value but now wants to create a linear/structured table with some of key/values. some kind of PIVOTING. Mysql does it by MAX CASING. (I have been there.) . @Nd'wex => a view may not be appropriate probably because of performance (in a view u will just be running the same query again and again from the parent.) which i suppose peter is why he is creating the next table because of performance issues. On Thu, Oct 24, 2013 at 12:22 PM, Nd'wex Common <flexycat@gmail.com> wrote:
@peter looking at the query seems like you are duplicating a tables structure and copying the respective record which in turn brings up the arguments by nelson (above) why not create a view?
tip: select * is more hungry resource wise that select id
_______________________________________________ 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
@Ndwex, nope, I am not duplicating table structure. I am following something I heard from some Facebook engineering video "store the data in the state its going to be used". The actual SELECT I use from my_table_b is a little bit more complicated. Like I mentioned, its a particularly nasty piece of SQL which takes 40 seconds to run, and does some calculations and transformations that honestly gives me headaches. And then you raise something interesting... A colleague once challenged me to practically and factually show him why *SELECT column_a, column_b, column_c FROM some_table *is better than *SELECT ** *FROM some_table*. EXPLAIN and EXPLAIN EXTENDED didnt show me any difference between the two. On Thu, Oct 24, 2013 at 12:31 PM, Ken Muturi <muturiken@gmail.com> wrote:
I think peter is trying to have data from one table to another table but in a different format. Perhaps the one table is a Key => value but now wants to create a linear/structured table with some of key/values. some kind of PIVOTING. Mysql does it by MAX CASING. (I have been there.) .
@Nd'wex => a view may not be appropriate probably because of performance (in a view u will just be running the same query again and again from the parent.) which i suppose peter is why he is creating the next table because of performance issues.
On Thu, Oct 24, 2013 at 12:22 PM, Nd'wex Common <flexycat@gmail.com>wrote:
@peter looking at the query seems like you are duplicating a tables structure and copying the respective record which in turn brings up the arguments by nelson (above) why not create a view?
tip: select * is more hungry resource wise that select id
_______________________________________________ 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
-- Regards, Peter Karunyu -------------------
@William, have you heard of LimeSurvey? Ages ago, it used to be one of the best open source "dynamic survey creation tool". It would create a database table for each survey the user would create. I don't know how SurveyMonkey or Google Forms does it, but I am using the approach LimeSurvey used to use. Granted, I could store all questionnaires and their stuff in the same tables, but for a busy setup, that would grow very big very fast, which in itself is not a problem, but I would rather solve many small problems than one gigantic one :-) @Adam, I thought there were two ways of checking the existence of a table; 1. Touch it as @Steve suggested, or 2. Check in information_schema, no? On Thu, Oct 24, 2013 at 10:36 AM, Adam Nelson <adam@varud.com> wrote:
@murithi
You bring up an important point. Ideally, schema migrations are done by a different database user than the one used by the application under normal use. This isn't commonly done but is definitely best practice.
Nonetheless, the schema migration has to be done via a user with rights to do it ... you can't create a table without the privileges to do so.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 10:43 PM, William Muriithi < william.muriithi@gmail.com> wrote:
I can't imagine that a mature framework like CodeIgniter would touch the table in order to check for the existence of that table.
Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons.
So optimization that is checking schema status is a wrong approach in my humble opinion
Muriithi
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord and a variety of other third party ORMs. I wrote the queries herein to make it easier to explain my situation.
On the (de)merits of using ORMs and other database abstractions, I believe thats a *discussion* for another day :-)
On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com> wrote:
@steve - you're right about the permission issues and the SELECT can
be a
good workaround
@peter - why are you writing SQL from a program? Presumably, whatever framework you're using has abstracted this stuff out, no?
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote:
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.
------------------------------
*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 <https://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 <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 <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 <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
-- Regards, Peter Karunyu -------------------
_______________________________________________ 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
I thought you were just using pseudocode to get the answer but that you would actually be using the CodeIgniter ORM to handle all of this stuff so it's really just academic, no? Anyway, even a 1M record table is super fast on a MySQL server so I'm not sure if this matters at all and even a 10M record table will respond to a one record SELECT in milliseconds. -- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson On Thu, Oct 24, 2013 at 12:38 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@William, have you heard of LimeSurvey? Ages ago, it used to be one of the best open source "dynamic survey creation tool". It would create a database table for each survey the user would create.
I don't know how SurveyMonkey or Google Forms does it, but I am using the approach LimeSurvey used to use.
Granted, I could store all questionnaires and their stuff in the same tables, but for a busy setup, that would grow very big very fast, which in itself is not a problem, but I would rather solve many small problems than one gigantic one :-)
@Adam, I thought there were two ways of checking the existence of a table; 1. Touch it as @Steve suggested, or 2. Check in information_schema, no?
On Thu, Oct 24, 2013 at 10:36 AM, Adam Nelson <adam@varud.com> wrote:
@murithi
You bring up an important point. Ideally, schema migrations are done by a different database user than the one used by the application under normal use. This isn't commonly done but is definitely best practice.
Nonetheless, the schema migration has to be done via a user with rights to do it ... you can't create a table without the privileges to do so.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 10:43 PM, William Muriithi < william.muriithi@gmail.com> wrote:
I can't imagine that a mature framework like CodeIgniter would touch the table in order to check for the existence of that table.
Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons.
So optimization that is checking schema status is a wrong approach in my humble opinion
Muriithi
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord and a variety of other third party ORMs. I wrote the queries herein to make it easier to explain my situation.
On the (de)merits of using ORMs and other database abstractions, I believe thats a *discussion* for another day :-)
On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com> wrote:
@steve - you're right about the permission issues and the SELECT
can be a
good workaround
@peter - why are you writing SQL from a program? Presumably, whatever framework you're using has abstracted this stuff out, no?
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote:
> 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. > > ------------------------------ > > *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 <https://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 <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 <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 <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
-- Regards, Peter Karunyu -------------------
_______________________________________________ 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
@Adam.. My MYSQL table is crawling at 500-600k. Perhaps an indexing problem though i have a few indexes on it.. On Thu, Oct 24, 2013 at 12:45 PM, Adam Nelson <adam@varud.com> wrote:
I thought you were just using pseudocode to get the answer but that you would actually be using the CodeIgniter ORM to handle all of this stuff so it's really just academic, no?
Anyway, even a 1M record table is super fast on a MySQL server so I'm not sure if this matters at all and even a 10M record table will respond to a one record SELECT in milliseconds.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Thu, Oct 24, 2013 at 12:38 PM, Peter Karunyu <pkarunyu@gmail.com>wrote:
@William, have you heard of LimeSurvey? Ages ago, it used to be one of the best open source "dynamic survey creation tool". It would create a database table for each survey the user would create.
I don't know how SurveyMonkey or Google Forms does it, but I am using the approach LimeSurvey used to use.
Granted, I could store all questionnaires and their stuff in the same tables, but for a busy setup, that would grow very big very fast, which in itself is not a problem, but I would rather solve many small problems than one gigantic one :-)
@Adam, I thought there were two ways of checking the existence of a table; 1. Touch it as @Steve suggested, or 2. Check in information_schema, no?
On Thu, Oct 24, 2013 at 10:36 AM, Adam Nelson <adam@varud.com> wrote:
@murithi
You bring up an important point. Ideally, schema migrations are done by a different database user than the one used by the application under normal use. This isn't commonly done but is definitely best practice.
Nonetheless, the schema migration has to be done via a user with rights to do it ... you can't create a table without the privileges to do so.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 10:43 PM, William Muriithi < william.muriithi@gmail.com> wrote:
I can't imagine that a mature framework like CodeIgniter would touch the table in order to check for the existence of that table.
Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons.
So optimization that is checking schema status is a wrong approach in my humble opinion
Muriithi
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord and a variety of other third party ORMs. I wrote the queries herein to make it easier to explain my situation.
On the (de)merits of using ORMs and other database abstractions, I believe thats a *discussion* for another day :-)
On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com>
wrote:
> @steve - you're right about the permission issues and the SELECT
can be a
> good workaround > > @peter - why are you writing SQL from a program? Presumably, whatever > framework you're using has abstracted this stuff out, no? > > > -- > Kili.io - OpenStack for Africa: kili.io > Musings: twitter.com/varud <https://twitter.com/varud>
> About Adam: www.linkedin.com/in/adamcnelson > > > On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote: > >> 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. >> >> ------------------------------ >> >> *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 <https://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 <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 <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 <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 >
-- Regards, Peter Karunyu -------------------
_______________________________________________ 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
@Muturi, welcome to the dark art of understanding how MySQL thinks. Simply because there is an index does not necessarily mean that MySQL will use it, see here<http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/>. This <http://www.fromdual.com/mysql-performance-tuning-key> might help in formulating an optimization plan. On Thu, Oct 24, 2013 at 12:50 PM, Ken Muturi <muturiken@gmail.com> wrote:
@Adam.. My MYSQL table is crawling at 500-600k. Perhaps an indexing problem though i have a few indexes on it..
On Thu, Oct 24, 2013 at 12:45 PM, Adam Nelson <adam@varud.com> wrote:
I thought you were just using pseudocode to get the answer but that you would actually be using the CodeIgniter ORM to handle all of this stuff so it's really just academic, no?
Anyway, even a 1M record table is super fast on a MySQL server so I'm not sure if this matters at all and even a 10M record table will respond to a one record SELECT in milliseconds.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Thu, Oct 24, 2013 at 12:38 PM, Peter Karunyu <pkarunyu@gmail.com>wrote:
@William, have you heard of LimeSurvey? Ages ago, it used to be one of the best open source "dynamic survey creation tool". It would create a database table for each survey the user would create.
I don't know how SurveyMonkey or Google Forms does it, but I am using the approach LimeSurvey used to use.
Granted, I could store all questionnaires and their stuff in the same tables, but for a busy setup, that would grow very big very fast, which in itself is not a problem, but I would rather solve many small problems than one gigantic one :-)
@Adam, I thought there were two ways of checking the existence of a table; 1. Touch it as @Steve suggested, or 2. Check in information_schema, no?
On Thu, Oct 24, 2013 at 10:36 AM, Adam Nelson <adam@varud.com> wrote:
@murithi
You bring up an important point. Ideally, schema migrations are done by a different database user than the one used by the application under normal use. This isn't commonly done but is definitely best practice.
Nonetheless, the schema migration has to be done via a user with rights to do it ... you can't create a table without the privileges to do so.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 10:43 PM, William Muriithi < william.muriithi@gmail.com> wrote:
I can't imagine that a mature framework like CodeIgniter would touch the table in order to check for the existence of that table.
Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons.
So optimization that is checking schema status is a wrong approach in my humble opinion
Muriithi
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
> @Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord > and a variety of other third party ORMs. I wrote the queries herein to make > it easier to explain my situation. > > On the (de)merits of using ORMs and other database abstractions, I believe > thats a *discussion* for another day :-)
> > > On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com> wrote: > >> @steve - you're right about the permission issues and the SELECT can be a >> good workaround >> >> @peter - why are you writing SQL from a program? Presumably, whatever >> framework you're using has abstracted this stuff out, no? >> >> >> -- >> Kili.io - OpenStack for Africa: kili.io >> Musings: twitter.com/varud <https://twitter.com/varud>
>> About Adam: www.linkedin.com/in/adamcnelson >> >> >> On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote: >> >>> 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. >>> >>> ------------------------------ >>> >>> *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 <https://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 <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 <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 <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 >> > > > > -- > Regards, > Peter Karunyu > ------------------- > > _______________________________________________ > 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 the links are interesting.. On Thu, Oct 24, 2013 at 12:59 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Muturi, welcome to the dark art of understanding how MySQL thinks.
Simply because there is an index does not necessarily mean that MySQL will use it, see here<http://www.mysqlperformanceblog.com/2009/09/12/3-ways-mysql-uses-indexes/>. This <http://www.fromdual.com/mysql-performance-tuning-key> might help in formulating an optimization plan.
On Thu, Oct 24, 2013 at 12:50 PM, Ken Muturi <muturiken@gmail.com> wrote:
@Adam.. My MYSQL table is crawling at 500-600k. Perhaps an indexing problem though i have a few indexes on it..
On Thu, Oct 24, 2013 at 12:45 PM, Adam Nelson <adam@varud.com> wrote:
I thought you were just using pseudocode to get the answer but that you would actually be using the CodeIgniter ORM to handle all of this stuff so it's really just academic, no?
Anyway, even a 1M record table is super fast on a MySQL server so I'm not sure if this matters at all and even a 10M record table will respond to a one record SELECT in milliseconds.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Thu, Oct 24, 2013 at 12:38 PM, Peter Karunyu <pkarunyu@gmail.com>wrote:
@William, have you heard of LimeSurvey? Ages ago, it used to be one of the best open source "dynamic survey creation tool". It would create a database table for each survey the user would create.
I don't know how SurveyMonkey or Google Forms does it, but I am using the approach LimeSurvey used to use.
Granted, I could store all questionnaires and their stuff in the same tables, but for a busy setup, that would grow very big very fast, which in itself is not a problem, but I would rather solve many small problems than one gigantic one :-)
@Adam, I thought there were two ways of checking the existence of a table; 1. Touch it as @Steve suggested, or 2. Check in information_schema, no?
On Thu, Oct 24, 2013 at 10:36 AM, Adam Nelson <adam@varud.com> wrote:
@murithi
You bring up an important point. Ideally, schema migrations are done by a different database user than the one used by the application under normal use. This isn't commonly done but is definitely best practice.
Nonetheless, the schema migration has to be done via a user with rights to do it ... you can't create a table without the privileges to do so.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 10:43 PM, William Muriithi < william.muriithi@gmail.com> wrote:
> I can't imagine that a mature framework like CodeIgniter would touch the > table in order to check for the existence of that table. > Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons.
So optimization that is checking schema status is a wrong approach in my humble opinion
Muriithi > -- > Kili.io - OpenStack for Africa: kili.io > Musings: twitter.com/varud <https://twitter.com/varud>
> About Adam: www.linkedin.com/in/adamcnelson > > > On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote: > > > @Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord > > and a variety of other third party ORMs. I wrote the queries herein to make > > it easier to explain my situation. > > > > On the (de)merits of using ORMs and other database abstractions, I believe > > thats a *discussion* for another day :-)
> > > > > > On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com> wrote: > > > >> @steve - you're right about the permission issues and the SELECT can be a > >> good workaround > >> > >> @peter - why are you writing SQL from a program? Presumably, whatever > >> framework you're using has abstracted this stuff out, no? > >> > >> > >> -- > >> Kili.io - OpenStack for Africa: kili.io > >> Musings: twitter.com/varud <https://twitter.com/varud>
> >> About Adam: www.linkedin.com/in/adamcnelson > >> > >> > >> On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote: > >> > >>> 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. > >>> > >>> ------------------------------ > >>> > >>> *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 <https://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 <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 <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 <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 > >> > > > > > > > > -- > > Regards, > > Peter Karunyu > > ------------------- > > > > _______________________________________________ > > 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 > > >
@Adam, yes, my initial question was purely academic on: 1. How MySQL interprets "*CREATE TABLE IF NOT EXISTS (SELECT some_column FROM some_table)*" 2. If splitting the query above into two: a) Check if table exists and b) Create table only if a) is false, would yield better performance. @Steve said that MySQL will evaluate the SELECT first, before the other two, i.e. IF NOT EXISTS and CREATE. This alone answered both questions. And yes, MySQL is able to handle a whole lot more pretty quick; but I am dealing with a database structure which I created more than 1 year ago, and that time I was young, naive and foolish, and did some things pretty badly, and they are coming to bite me now. On Thu, Oct 24, 2013 at 12:45 PM, Adam Nelson <adam@varud.com> wrote:
I thought you were just using pseudocode to get the answer but that you would actually be using the CodeIgniter ORM to handle all of this stuff so it's really just academic, no?
Anyway, even a 1M record table is super fast on a MySQL server so I'm not sure if this matters at all and even a 10M record table will respond to a one record SELECT in milliseconds.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Thu, Oct 24, 2013 at 12:38 PM, Peter Karunyu <pkarunyu@gmail.com>wrote:
@William, have you heard of LimeSurvey? Ages ago, it used to be one of the best open source "dynamic survey creation tool". It would create a database table for each survey the user would create.
I don't know how SurveyMonkey or Google Forms does it, but I am using the approach LimeSurvey used to use.
Granted, I could store all questionnaires and their stuff in the same tables, but for a busy setup, that would grow very big very fast, which in itself is not a problem, but I would rather solve many small problems than one gigantic one :-)
@Adam, I thought there were two ways of checking the existence of a table; 1. Touch it as @Steve suggested, or 2. Check in information_schema, no?
On Thu, Oct 24, 2013 at 10:36 AM, Adam Nelson <adam@varud.com> wrote:
@murithi
You bring up an important point. Ideally, schema migrations are done by a different database user than the one used by the application under normal use. This isn't commonly done but is definitely best practice.
Nonetheless, the schema migration has to be done via a user with rights to do it ... you can't create a table without the privileges to do so.
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud> About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 10:43 PM, William Muriithi < william.muriithi@gmail.com> wrote:
I can't imagine that a mature framework like CodeIgniter would touch the table in order to check for the existence of that table.
Think a production application should not be able to alter the database schema. Should only be able to insert, update, select and delete for security reasons.
So optimization that is checking schema status is a wrong approach in my humble opinion
Muriithi
-- Kili.io - OpenStack for Africa: kili.io Musings: twitter.com/varud <https://twitter.com/varud>
About Adam: www.linkedin.com/in/adamcnelson
On Wed, Oct 23, 2013 at 5:34 PM, Peter Karunyu <pkarunyu@gmail.com> wrote:
@Adam, yes, the framework I use (CodeIgniter) allows usage of ActiveRecord and a variety of other third party ORMs. I wrote the queries herein to make it easier to explain my situation.
On the (de)merits of using ORMs and other database abstractions, I believe thats a *discussion* for another day :-)
On Wed, Oct 23, 2013 at 5:23 PM, Adam Nelson <adam@varud.com>
wrote:
> @steve - you're right about the permission issues and the SELECT
can be a
> good workaround > > @peter - why are you writing SQL from a program? Presumably, whatever > framework you're using has abstracted this stuff out, no? > > > -- > Kili.io - OpenStack for Africa: kili.io > Musings: twitter.com/varud <https://twitter.com/varud>
> About Adam: www.linkedin.com/in/adamcnelson > > > On Wed, Oct 23, 2013 at 5:02 PM, Steve Obbayi <steve@sobbayi.com> wrote: > >> 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. >> >> ------------------------------ >> >> *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 <https://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 <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 <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 <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 >
-- Regards, Peter Karunyu -------------------
_______________________________________________ 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 (5)
-
Adam Nelson -
Ken Muturi -
Nd'wex Common -
Peter Karunyu -
William Muriithi