Re: load data into temporary table
Thank you but the real problem occurs when you don't know the schema of the table in advance. If data.txt has two columns columns how can I still load it in a temporary table? I'm asking this question because I'd like to add an import csv feature to a web application. I know that you can load data infile into table without specifying the schema of this table but it does not look like you can do load data infile into a temporary table. Thank you, Alex 2009/5/19 Janek Bogucki : > Hi, > > mysql> create temporary table t(i int); > > mysql> \! echo 1 > /tmp/data.txt > > mysql> load data infile '/tmp/data.txt' into table t; > Query OK, 1 row affected (0.00 sec) > Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 > > mysql> select * from t; > +--+ > | i | > +--+ > | 1 | > +--+ > 1 row in set (0.00 sec) > > Best Regards, > -Janek, CMDEV 5.0. > StudyLink. Helping People Realise Their Potential. > http://studylink.com > > > On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote: >> Hello, >> >> Would anyone know how to load data infile into a temporary table? >> >> Thank you, >> >> Alex >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=alex.ksi...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
load data into temporary table
Hello, Would anyone know how to load data infile into a temporary table? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: slow "in" statement?
Here we go: http://pastebin.com/m2439985d replace $company_ids by list of ids from 'companies' replace $neg_company_ids by -1 * $company_ids replace $location_ids by list of location ids from 'locations' replace $all_company_ids by list of ids from 'all_companies' The reason why I'm doing $neg_company_ids is because I want to differentiate between ids from 'companies' and from ids 'all_companies'. So I noticed that when I replace the in statements by ors, it is actually faster. Thank you so much. 2008/11/10 Ananda Kumar <[EMAIL PROTECTED]>: > can u please show me the explain plan for this sql and also the table > structure > > explain select statement > > desc table_name > > > On 11/10/08, Alex K <[EMAIL PROTECTED]> wrote: >> >> Hi Ananda, >> >> Indexes are present. I'm passing no more 10 values. >> >> Alex >> >> 2008/11/10 Ananda Kumar <[EMAIL PROTECTED]>: >> > it should not cause any issues, unless your passing too many values in >> "id >> > in(1,2,...n). >> > Are the indexes present. >> > >> > >> > On 11/10/08, Alex K <[EMAIL PROTECTED]> wrote: >> >> >> >> Hello, >> >> >> >> It seems to me that the statement "id in (id1, id2 ... idn)" is much >> >> slower than "id=id1 or id=id2 ... or id=idn" or I am doing something >> >> wrong? >> >> >> >> Thank you, >> >> >> >> Alex >> >> >> >> -- >> >> MySQL General Mailing List >> >> For list archives: http://lists.mysql.com/mysql >> >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> >> >> >> > >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow "in" statement?
Hi Ananda, Indexes are present. I'm passing no more 10 values. Alex 2008/11/10 Ananda Kumar <[EMAIL PROTECTED]>: > it should not cause any issues, unless your passing too many values in "id > in(1,2,...n). > Are the indexes present. > > > On 11/10/08, Alex K <[EMAIL PROTECTED]> wrote: >> >> Hello, >> >> It seems to me that the statement "id in (id1, id2 ... idn)" is much >> slower than "id=id1 or id=id2 ... or id=idn" or I am doing something >> wrong? >> >> Thank you, >> >> Alex >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] >> >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow "in" statement?
Hello, It seems to me that the statement "id in (id1, id2 ... idn)" is much slower than "id=id1 or id=id2 ... or id=idn" or I am doing something wrong? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple design choice
That seems like a nice trick. I suppose the flag would just be an int and not an enum in this case. 2008/10/3 Mr. Shawn H. Corey <[EMAIL PROTECTED]>: > On Fri, 2008-10-03 at 09:58 -0700, Rob Wultsch wrote: >> On Fri, Oct 3, 2008 at 9:49 AM, Alex K <[EMAIL PROTECTED]> wrote: >> > Hello, >> > >> > I have a table of a 1 million users. I want to add a flag called >> > delete if a user wants to delete his account. Note that this situation >> > does not happen a lot. >> > >> > 1) Should I alter my users table and add a delete flag to the users table. >> >>> it's easy to update however it uses a lot of unnecessary space. >> > >> > 2) Should I create a new table user_id, flag already prefilled with >> > all user_ids. >> > >> > 3) Should I create a new table called deleted_users that has a user_id >> > if this user wants to be deleted. >> >>> it's hassle to update but takes into consideration the spareness of the >> >>> data. >> > >> > Thank you, >> > >> > Alex >> >> #1 Define uses a lot of unnecessary space. I would imagine it would >> add not much more than 1 MB to the size of db, depending on column >> choice. A decent choice I think. >> >> #2 Yuck. >> >> #3 A compact and clean solution. >> > > If you're going to do #1, make the new column "status", with two states: > active and deleted. In the future you can add more states without > re-doing your tables again. > > > -- > Just my 0.0002 million dollars worth, > Shawn > > Linux is obsolete. > -- Andrew Tanenbaum > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: simple design choice
2) is probably cleaner but a hassle as well because one needs to make sure all user_ids are also in this new table. 2008/10/3 Alex K <[EMAIL PROTECTED]>: > Hello, > > I have a table of a 1 million users. I want to add a flag called > delete if a user wants to delete his account. Note that this situation > does not happen a lot. > > 1) Should I alter my users table and add a delete flag to the users table. >>> it's easy to update however it uses a lot of unnecessary space. > > 2) Should I create a new table user_id, flag already prefilled with > all user_ids. > > 3) Should I create a new table called deleted_users that has a user_id > if this user wants to be deleted. >>> it's hassle to update but takes into consideration the spareness of the >>> data. > > Thank you, > > Alex > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
simple design choice
Hello, I have a table of a 1 million users. I want to add a flag called delete if a user wants to delete his account. Note that this situation does not happen a lot. 1) Should I alter my users table and add a delete flag to the users table. >> it's easy to update however it uses a lot of unnecessary space. 2) Should I create a new table user_id, flag already prefilled with all user_ids. 3) Should I create a new table called deleted_users that has a user_id if this user wants to be deleted. >> it's hassle to update but takes into consideration the spareness of the data. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
backup questions
Hi guys, Is it safe to perform a backup of a database by copying its mysql files if the database is: 1) MyISAM and the database is not being used (mysql server turned off)? 2) InnoDB and and the database is not being used? 3) MyISAM and the database is being used? 4) InnoDB and and the database is being used? I know I can use mysqhotcopy or mysqldump but I'm just curious if it's possible to perform a backup by simply copying the files over in the conditions described above. Thank you so much, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of heterogeneous joins
Cool it's good to know thank you. On 25/01/2008, Jay Pipes <[EMAIL PROTECTED]> wrote: > Nope, no difference, AFAIK. > > Alex K wrote: > > Any ideas pertaining this newbie question? > > > > Thank you so much, > > > >> Hi Guys, > >> > >> Is there a performance hit when joining across multiple databases as > >> opposed to joining multiples tables in one database? Suppose the same > >> tables are available across all databases. > >> > >> Thank you, > >> > >> Alex > >> > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance of heterogeneous joins
Any ideas pertaining this newbie question? Thank you so much, > Hi Guys, > > Is there a performance hit when joining across multiple databases as > opposed to joining multiples tables in one database? Suppose the same > tables are available across all databases. > > Thank you, > > Alex > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select from otherdb.table question?
Hi Brent, ahh of course :) thank you so much for answering though. Alex On 20/01/2008, Brent Baisley <[EMAIL PROTECTED]> wrote: > When you establish a connection, it's a connection to a server, not a > specific DB. You can set a default db so that you don't always have > to specify the db name you are working with. So to answer your > question, no, a new connection is not established. > > Brent > > > On Jan 19, 2008, at 10:19 AM, Alex K wrote: > > > Hi Guys, > > > > What does the statement "select * from otherdb.table" do if I haven't > > explicitly connected to otherdb previously? I would assume it connects > > to otherdb and does the select on table but does it create a new > > connection each time? Is it as efficient as explicitly connecting to > > otherdb and then querying. I'm using webware DBUtils for connection > > pooling. Would these connections also be taken into account? > > > > Thank you so much, > > > > Alex > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe:http://lists.mysql.com/mysql? > > [EMAIL PROTECTED] > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select from otherdb.table question?
Hi Guys, What does the statement "select * from otherdb.table" do if I haven't explicitly connected to otherdb previously? I would assume it connects to otherdb and does the select on table but does it create a new connection each time? Is it as efficient as explicitly connecting to otherdb and then querying. I'm using webware DBUtils for connection pooling. Would these connections also be taken into account? Thank you so much, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance of heterogeneous joins
Hi Guys, Is there a performance hit when joining across multiple databases as opposed to joining multiples tables in one database? Suppose the same tables are available across all databases. Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: basic style shema question
Hi Kevin, Well the basic information, company description and personalized options will be selected many times (whenever a user submits a query). It will basically be show on the result page of the search engine. The user's login / password well is used to login, then the user may update the company basic information, description and personalized options. These updates may happen sporadically though. Once every 3 minutes these fields are selected again in order to update the search engine index. Thank you, Alex On 18/01/2008, Kevin Hunter <[EMAIL PROTECTED]> wrote: > At 11:44a -0500 on 18 Jan 2008, Alex K wrote: > > To summarize one table vs. many tables with one to one relations? > > As per usual, it depends on your needs. For most flexibility, and to > give the DB the best chance to give the best plan for the possible > requests I might make in the future, I generally tend towards > normalization, not denormalization. > > The question is: what do you want to do with the schema? > > Kevin > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
basic style shema question
Hi Guys, Let's suppose I have the following table: create table companies ( id int(11) not null auto_increment primary key, # user's login and password email varchar(100), passwordvarchar(100), # basic information company_namevarchar(100), contact_namevarchar(100), street varchar(100), cityvarchar(100), state varchar(7), zip varchar(13), phone varchar(25), # user's company description description text, category_other text, # localization desired_zip varchar(7), latitudedec(10,7), longitude dec(10,7), # user's personalized options url varchar(200) not null unique, logo_md5varchar(32), linked_url varchar(200), color_bgrd varchar(16), # content moderation (null if nothing, 1 for new, 2 for updates) updates smallint, banned boolean ); Would you keep this as one table or would you split it into multiple tables such as users, company localizations, personalized options and moderation which would hold each the fields under each comment together with a company_id? The first way of doing it is easier to update because I would not have to update all the other linked tables. But the other way of doing seems cleaner. To summarize one table vs. many tables with one to one relations? Thank you, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
