|
I have a MySQL database with one particular MyISAM table of above 4 million rows. I update this table about once a week with about 2000 new rows. After updating, I then perform the following statement:
ALTER TABLE x ORDER BY PK DESC
i.e. I order the table...
Started by Timothy Mifsud on
, 6 posts
by 6 people.
Answer Snippets (Read the full thread at stackoverflow):
Anyway....
I am surprised in production.
Query is useful to improve performance in certain scenarios.
ORDER BY ...
As I have just read, the ALTER TABLE ...
Don't need to lock up that huge table while the ALTER is being performed.
|
|
Hello,
we are trying to minimize (maintenance) downtimes of our mysql based application.
It seems that InnoDB hotbackup will give us the possibility to do regular backups without stopping the server; Master/Slave replication will give us failover capabilities...
Started by filibuster on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
This doesn't work for all ALTER TABLE commands, but it can handle the majority of themThe best ....
With ALTER TABLE on the slave, let the slave catchup after it is done, swap roles, and then ALTER on the former master.
|
|
When doing an ALTER TABLE statement in MySQL, the whole table is read-locked for the duration of the statement. If it's a big table, that means insert or update statements could be locked for a l ng time. Is there a way to do a "hot alter", like adding...
Started by Daniel on
, 10 posts
by 10 people.
Answer Snippets (Read the full thread at stackoverflow):
Without rebuilding the....
Operate differently.) In any case, you can copy the table to another table, alter the Innodb plugin, ALTER TABLE statements which only add or drop secondary indexes can be done "quickly", i.e.
|
Ask your Facebook Friends
|
I am writing a plugin for wordpress. When the plugin is initialised I need to find out if the users table contains the columns I am trying to insert to ensure I am not overwriting anything. Can someone provide me with the syntax that does this; I think...
Started by Drew on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
Schema.COLUMNS WHERE COLUMN_NAME = 'column_name' AND TABLE_NAME = 'table_name' AND TABLE_SCHEMA = 'database_name' LIMIT 1
pseudocode:
DBQuery("SHOW COLUMNS FROM ".$table); while (DBGetRow()) { $columns.
|
|
In the same way SHOW CREATE TABLE tblname; brings back what was previously executed, is there anyway to view the SQL of an ALTER TABLE query? please help?
Started by Chloe McDermott on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
Small clarification: show create....
First table name to find the alter table statement you are looking for.
As for finding any alter table statements that ran on the table recently, the best bet is the binary log.
|
|
Create table check2(f1 varchar(20),f2 varchar(20));
creates a table with the default collation latin1_general_ci;
alter table check2 collate latin1_general_cs; show full columns from check2;
shows the individual collation of the columns as 'latin1_general...
Started by dta on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
If you change the collation of the server, database or table, you dont change whe....
It sets the default collation for the table; if you create a new column, that should be collated levels of collation, server, database, table, column.
|
|
I have a table in PostgreSQL where the schema looks like this:
CREATE TABLE "foo_table" ( "id" serial NOT NULL PRIMARY KEY, "permalink" varchar(200) NOT NULL, "text" varchar(512) NOT NULL, "timestamp" timestamp with time zone NOT NULL )
Now I want to ...
Started by Baishampayan Ghose on
, 3 posts
by 3 people.
Answer Snippets (Read the full thread at stackoverflow):
So, it should be something like
ALTER TABLE foo ADD UNIQUE CONSTRAINT (thecolumn)
I figured it out from the PostgreSQL docs, the exact syntax is:
ALTER TABLE the_table ADD CONSTRAINT.
In Postgres.
|
|
I'm working with an old Access database (yes, it's very ugly and I hate it). I need to modify some of the columns from a VB app that I'm creating. I have most the modifications setup correctly, but I'm fighting with the fact that modifying a column to...
Started by Ryan Smith on
, 5 posts
by 5 people.
Answer Snippets (Read the full thread at stackoverflow):
Something like:
ALTER TABLE [Applicant Table] ADD CONSTRAINT As TableDef Dim fld As DAO.Field Set db = CurrentDb Set tbl = db.TableDefs![Applicant Table] Set fld.
Is to use a CHECK constraint e.g.
|
|
I want to programmatically create a new column in an Access table. I've tried many permutations of "ALTER TABLE MyTable Add MyField DECIMAL (9,4) NULL;" and gotten "Syntax Error in Field Definition". I can easily create a number field that goes to a Double...
Started by CindyH on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
CurrentProject.Connection.Execute "ALTER TABLE myTbl ADD COLUMN myColumn DECIMAL(9,4 this into the SQL window:
....
Try an ADO connection e.g.
The ALTER TABLE syntax is only supported in Jet 4.0/ACE while in ANSI-92 Query Mode.
|
|
I have a need to change the length of CHAR columns in tables in a PostgreSQL v7.4 database. This version did not support the ability to directly change the column type or size using the ALTER TABLE statement. So, directly altering a column from a CHAR...
Started by E Brown on
, 4 posts
by 4 people.
Answer Snippets (Read the full thread at stackoverflow):
I would dump the table contents to a flat file with COPY, drop the table, recreate described requires making the table unusable for a period of time, how long depends on the data.
|
