MYSQL :: Create Table Syntax For Large Tables

Large Table Or Many Tables?
I posted a similar question about 6~12 months ago with no definate answer, so I will give it another go, as the choice has popped up again in my programming life:

If the number of entries is the same is it more efficient (better) to have a single table with many columns, or many tables with few columns? The obvious answer is a single table, because there is less closing and opening tables, but the reason why I ask is what about searching through a lot of columns being even more inefficient?

The situation is I need to record data for every week of the year. Say ~50 columns per week --> 2600 columns. Or, do I split it so it's 52 tables with 50 columns each? In each of these options the user (or user ID) will be the primary key.

The seemingly pointless other option is to insert new columns with every new user, and have the week-item as the primary key?

View Replies !

One Large Table Or Many Small Tables?
I'm trying to decide whether to use one large table or many small tables.
I need to gather information from various devices (about 500). Each device
has its own Id and some data.

Should I use only one table with an indexed column for the ID and another
column for the data, or should I use 500 tables each with only one column
for the data?

View Replies !

One Large Table Or Several Smaller Tables
From a speed of access standpoint, is it better to have one large table or several smaller tables? I'm in the early stages of development, so I can go either direction with this.

View Replies !

One Large Table Vs. Many Small Tables
I'm working on a design using PHP & MySQL and I'd like to get some opinions on this.

My design has several tables that will be referenced but I'm wondering if those tables should be broken down even more and referenced more dynamically. The reason that I wonder about this is for long term goals. I hope that eventually there will be two or three thousand records that will be used on a regular basis. These records will need to be separated into groups, but I'm not sure if I should use a field in the database table or create a new table for each group.

If a few hundred records could be in each group, do you think it's better to use one large table with a field for the group ID, or a new table for each group?

View Replies !

Create Table Error. #1064 - You Have An Error In Your SQL Syntax; Check The Manual That Corresponds
I have been trying to create two tables with the SQL below. I have the SQL in file and tried to import it to PhpMysqlAdmin in my control panel. The wierd thing is that the first table gets created while the second one is not created and error is thrown instead. The error message is

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ....

View Replies !

Create A New Table By Joining Two Tables Together
TableA and TableB have one field in common: studynumber.

This works:

SELECT * FROM main3 JOIN studies_001 ON main3.studynumber = studies001.studynumber;

Now I want to create a new table based on that JOIN.

I can't seem to do it - I changed the fieldname in one table to snumber to avoid duplicate fields but this does not work:

CREATE TABLE studies_try1 SELECT (main3.studynumber, main3.studydate, main3.studytitle, main3.studyintro, main3.studytext, main3.booknumber, main3.messagetype, main3.media2_filename, main3.teacher_id, main3.media1_filename,studies_001.id, studies_001.snumber, studies_001.chapter_begin, studies_001.verse_begin, studies_001.chapter_end, studies_001.verse_end) JOIN studies_001 ON main3.studynumber = studies_001.snumber;

View Replies !

Create Multiple Tables From Table
I have a table called 'product' in a sql database called 'product_comparison'.
There are currently 3 fields in this table. ProductNumber (which is charvar - 255), Description (which is charvar - 255), and Baillio's (which is decimal 20,2 **it's a dollar figure**).

I have just under 10,000 lines (different products) and I'm wanting to create a table for each of the products. The tables should be named whatever the 'Product Number' is. Which is usually a combination of letters and numbers (thus the charvar). I also want to take the other 2 values and create 2 fields in their respective product tables.

I'm thinking this should be an easy statement for people as smart as all of you. But I'm having trouble. So far, (with some help) I've come up with a statement something like this:

CREATE TABLE PAGEONE SELECT * FROM product WHERE `ProductNumber` = 2;

This doesn't work becuase 'ProductNumber' isn't an integer first off, and I'm thinking even if it did work, it would still just create one table called PAGEONE and not a table for each product like I want. Is that right?

View Replies !

Create A League Table From Two Tables?
2 tables, Student and teacher and they look like this:

Student | Teacher
Student_id | Teacher_id
student_name | teacher_name
student_class | teacher_class
student_praise | Teacher_total_praise

Each teacher is has a registration class and each student belongs to a class.

A student gets praise in lessons for being good.

I want to create a php table on my webpage that shows all of the teacher_class in order of teacher_total_praise.

View Replies !

How To Create A Table (many-to-many Association) From The Primary Keys Of Tables?
In mysql, I need to create a table to store the many-to-many relationship of the other two tables.

Here is the problem:

the first table has three fields that act as the primary key of the table

the second table has only one field for the primary key

i know the association table will have the primary keys of both tables as its own primary key but it seems that I can't do that. It appears that the three fields primary key in the first table is giving me some trouble!

Is this allowed in MYSQL? If so, could someone please provide a sample sql code.

View Replies !

How To Create A Join/relationship Table That Maps To Many Tables
Let's say I had the following table structure:

CREATE TABLE customer
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
password VARCHAR(32) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service1
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service2
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE service3
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service
(
subscription_id INT UNSIGNED NOT NULL,
service_type TINYINT UNSIGNED NOT NULL,
service_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service_type, service_id)
) ENGINE = INNODB;

CREATE TABLE service_type
(
id TINYINT UNSIGNED NOT NULL,
service_table_name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE = INNODB;
INSERT INTO service_type (id, service_table_name) VALUES
(1, 'service1'),
(2, 'service2'),
(3, 'service3');
I need a way to map Customers to Subscriptions to Services.

Please keep in mind that one subscription record can be related to many services: even services of the same type.

The above structure would work, but I wouldn't be able to make it transactional. The problem lies with the subscription_2_service table's service_id column. That column doesn't actually reference any other table. It's conditional, meaning its value changes depending on what the service type column value is.

This makes it impossible to implement transactions because the service_id column references nothing concrete.

I considered another way of doing the above by sticking a subscription_id column in all three service tables. I don't like this approach because not all services will require a subscription. This would result in many NULL values and tight coupling between the subscription and service tables. It also seems sloppy/scattered.

I also considered a third way of creating subscription_2_service tables, such as:

CREATE TABLE subscription_2_service1
(
subscription_id INT UNSIGNED NOT NULL,
service1_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service1_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service2
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;

CREATE TABLE subscription_2_service3
(
subscription_id INT UNSIGNED NOT NULL,
service3_id INT UNSIGNED NOT NULL,
PRIMARY KEY (subscription_id, service3_id)
) ENGINE = INNODB;
This third method above seems ridiculous.

The third approach does decouple the subscriptions and services from one another. Services aren't aware that they're part of a subscription and subscriptions aren't aware of what services have subscribed to them. This method still seems bloated and wrong though.

View Replies !

Create New User ... Error In Syntax?
I'm trying to create a new user using this statement:

mysql> CREATE USER 'user1'@'localhost'
-> IDENTIFIED BY 'userpass';
ERROR 1064: You have an error in your SQL syntax near ''user1'@'localhost'
IDENTIFIED BY 'userpass'' at line 1

My mysql status:

mysql> status
--------------
mysql Ver 9.38 Distrib 3.22.32, for pc-linux-gnu (i686)

The mysql server is on my webhost and I need to SSH to do this.
Can you guys enlighten me on what does the error means?

View Replies !

Create Procedure -> Syntax Error
I try to create the stored procedure as follows.

CREATE PROCEDURE backup.writeData(IN key_id varchar(32))
BEGIN
insert into backup.tmp_deletetable(
select ism_id
from backup.in_stu_masterdata_hist
where concat(biscountry,bisid) = key_id
order by ism_id desc
limit 10,9999);
END;

But everytime I get the error. "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1"

Whats wrong. I think the statement is correct.
My DB version is 5.0.45 on Win XP.

View Replies !

CREATE PROCEDURE DECLARE Syntax Error
I'm just getting back into working with mySQL after years away from it...I regularly work with SQL Server 2005, and right away am stumbling over syntax issues. Can anyone tell me what is wrong with the following stored procedure definition? It fails right on the first few lines:....

View Replies !

Large Tables
So i've got a table with 15 million rows. id is the primary key

SELECT COUNT(id) FROM listings WHERE id != 0;

Takes about a minute to load... which is insanely slow..
anyone know what could be causing this?

View Replies !

Very Large Tables
I am looking at using mysql to store some particle physics data. So the idea is (for example) I would have a table with a list of events, then a table with a list of particle types (one event can have many particles) and so on. The problem is that quite often I want to calculate something for every single event, but this table might run into 10s of millions of events and many GB. Obviously it would be mad to load the whole result set into memory then iterate over it. My question is: is there any way to have the db push results to the users one (or a few) at a time in an effiecent way? I was thinking of something like an iterator that pulls the data from the disk one (or few) at a time. An alternative (but much less useful) method might be to hand the function I want to calculate to mysql, for example:
select myfunc(x,y,z,a,b,c) from events where energy>3;
But as far as I know there is no way of defining your own functions in SQL

View Replies !

Large Number Of Tables
I would like to ask whether it is feasible (or whether it makes sense) to run a PHP script that creates a number of tables for each user.
Basically, this script allows users to sign up for a free service, and for each user that signs up, the script creates 9 tables in the database to contain that user's details.
Is this a sensible way of containing user data, and what are the inherent problems this may cause especially when say are 1000 users signup?

View Replies !

Large DB Should Use Multiple Tables?
I have a social networking site similar to myspace, currently my table which links who is who's friend on my site is 2 weeks old and is at 300,000 rows but is only at 15mb in size, this is the most accessed table of all my tables.

My question for performance, as this table grows should it eventually make a new table? If so here is my current select code

select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'

Could someone give me an example of how o select this from multiple tables if I add more for this table in the future?

View Replies !

Managing Large Tables
I recently inherited a database (version 5.0.18) that has 1 table constantly growing out of hand between 10GB -30GB, therefore making is difficult/impossible to query. Also, the Archive Storage Engine is not installed. Currently, the table is manually renamed (i.e. tablename_daterange) and a new table created. ....

View Replies !

Join Tables On A Large Database 200 Meg
i am trying to work out the most efficient way to list say multipl=
e categories of entries, the database is quite large about 200 meg.=20

I would like to know if using join tables is more efficient than storing th=
e keys in a varchar field then within the second loop doing a where in (1,2=
,3,4) where the 1,2,3,4 are the stored category keys in the varchar field =
rather than a where in (1), where 1 is the pirmaryID of the entry for insta=
nce ?

View Replies !

Optimizing Mysql For Large Tables
I have been working with Mysql for about 5 years - mainly in LAMP shops. The tables have been between 20-100 thousand records size. Now I have a project where the tables are in the millions of records.
This is very new to me and I am noticing that my queries are really slowwwwww!
What are the options that I have to speed my queries on the mysql side with regards to the my.cnf file. I have a fair understanding of sql optimization and I understand explain. I just want to see if there is something that I can do with the server also.

View Replies !

Slow Join On Large Tables
I have two tables:

D (500,000 recs), and DL (2,500,000 recs)

D has a PK and an index on HLQ. DL has a PK and an index on ID.

The following SQL:

SELECT
HLQ as "HLQ",
count(*)
FROM
D, DL
WHERE
D.DLID=DL.ID
GROUP BY HLQ

produces the following explain:
tabletypepossible_keyskeykey_lenrefrowsExtra
DALL500000Using where; Using temporary; Using filesort

DLeq_refIDID4D.DLID1Using index

The query takes ~ 3:30 on a Athlon xp2200; 1GB RAM; default bufer settings. Adding the following buffer settings only slightly decrerased the time (~3:00).

key_buffer=512M
table_cache=256
sort_buffer=16M
read_buffer_size=16M

It appeasrs that the 'Using filesort' on table D is due to the Group
By clause and is the problem. I have an index on HLQ. Is there any
way to get MySQL to use it?

View Replies !

Large Tables, Very Slow Deletes.
I've been using MySQL for a while now and are starting to run into limitations. Either my own, or something else.

I have this really large table, it stores images, it has a mediumblob field, an unsigned id integer field and a timestamp field. This table contains up to a few million rows and is constantly filled real time. It grows up to sizes between 50 and 100 gb and in the future probably even larger.

This data is not supposed to be stored for all time, therefor when a certain treshhold is reached, the oldest 5000 rows are deleted to make room for newer rows. This is where my problem kicks in. Whenever I try to delete those rows, it can take for ever to complete. 500 rows I can delete without problem, 2500 becomes slow, 5000 rows takes several seconds and 10.000 rows or more makes it looks like things are frozen.

Now the temporary solution I have is to delete 500 rows at time, but this means I must do that every 10 seconds just to maintain status quo. I would much rather check every 5 minutes or so, and if needed delete 15.000 rows in one go.

I use MyISAM tables and tried setting the key size to 128M, no luck. I run this on a dual Opteron system with a GB of memory and WinXP-SP2 Proffesional.

View Replies !

Large Unused Tables Vs Performance
I've been working on a website with a rather large a database. One of the about 100 tables in there was about 1/4 of the total database size.

The table had no use anymore at the time I worked the website, so it was just sitting there, doing absolutely nothing. Obviously to spare diskspace we deleted the table.

But the question I have now, can unused large tables affect the perfomance of Mysql?
I've searched a bit around, but honestly I couldn't find a clear answer about it right away (maybe I haven't searched enough either :) )

View Replies !

Large Number Of Tables And Columns
i'm developing an online portal, which my MYSQL database contains 100 tables.. my doubt is if any table contains 30-40 columns will it become a problem? like my html front end form has 40 fields for data insertion. and i'm not maintaing any duplicate data if my DB contains 100-150 tables will it create any problems?

as i'm inserting data from EXCEL via PHP to MYSQL, please suggest me whether a healthy MYSQL table is dependent or independent on NO:OF COLUMNS(FIELDS) or not?

View Replies !

Speeding Up Large MySQL MyISAM Tables
I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.

Perhaps someone can help me with indexes here; I was under
the impression that the index for the tables are cached in memory, and
therefore permitted "instant" searchability, without having to retrieve data
from each of the rows of the DB. Is there a startup parameter, or
something in the mysql.ini file, that must be set to allow for this? I have
the index configured properly, and have made sure that the query uses there
parameters in the where clause in the same order that they appear in the
index. Code:

View Replies !

Random Records :: Rand() On Large Tables?
Im told that ORDER by rand() isnt a good idea to use on large tables, so Im interested to know how else this could be done. I need to display 20 random records (there are conditions in the WHERE clause as well).

View Replies !

Loading Large Tables From Files (wikipedia)
I'm an Oracle DBA and new to MySQL.
I was trying to load a 5.6 GB xml file onto mysql database using mediawiki tools.
The performance decreased gradually and crashed at ~60% completion.
Now I'm planning to use xml2sql tools to convert the dumps to txt format.
Then I'm planning to load this using mysqlimport.
I would like to run this load faster, and on Oracle, I would use the IMP parameters
such as INDEXES=n and so on. What are the recommended steps when using mysql?
Should I target any of the system variables?
I have a 4G ram on the server and can use all of it for the load.
Any pointers?

View Replies !

Time Out Message When Query Large Tables
I'm trying to get data from 6 large tables but the volume of data in each table is too large and even select * from one of them make the system stop. I have afew questions:

1-what can I do to avoid the system stop or time out message?

2- To use several tabels infomation should I use 'View' command or can I use other methods?

3-I need to create a new table and insert the result from query in it. If I use the "view" can I insert the result of the view in a table?

(I use postgresql).

View Replies !

Selecting From Multiple Large Tables Quickly
My skills with MySQL typically end at "SELECT * FROM table WHERE stuff", so I've had a lot of trouble with optimizing this query. I've tried doing multiple select statements for this, but that usually comes out with an average execution time of 99s. This method has a execution time of 6s still, so I'd like to get it down.

I have 4 important tables here (I'm only posting the columns which I use):

`auth`

UID mediumint(8) unsigned PRI NULL auto_increment
username varchar(32) MUL
clan_id mediumint(8) unsigned MUL 0
clan_abbrev varchar(7) MUL
`clans`

ID mediumint(8) unsigned PRI NULL auto_increment
Abbrev varchar(7) MUL
Name varchar(64)
icon_id smallint(5) unsigned 0
`stats`

UID int(10) unsigned PRI 0
kills mediumint(9) NULL
`usernames`

UID int(10) unsigned 0
ID int(10) unsigned PRI NULL auto_increment
Name varchar(32)
Uses mediumint(9) 0
`auth`, `stats`, and `usernames` are linked by `UID`. `clans` is linked to a row in `auth` based on it's `clan_id`.

I need to search the `usernames` table for a `Name` which matches a certain text (I use "storm" in my example below). I need to return a result with matches to that search, with each row containing:The `UID` which links the tables together for that user.The matching `Name` from `usernames`The matching `username` from `auth`The `Uses` from `usernames`.The `clan_id` from `auth`.The `Abbrev` from `clans`.The `Name` from `clans`.The `icon_id` from `clans`.The `kills` from `stats`.
The result should be ordered by `Uses` (from `usernames`) -- highest to lowest.

The tricky part is that some users may have Ɔ' as their `clan_id` in the `auth` table, in which case the clan id, abbrev, name, and icon_id should all be blank (0 or '' based on the type).

Here's what I managed to hammer out with my limited knowledge of SQL:

(
SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , auth.clan_id AS `clan_id` , clans.Abbrev AS `clan_abbrev` , clans.Name AS `clan_name` , clans.icon_id AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `clans` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND clans.ID = auth.clan_id
AND stats.UID = auth.UID
)
UNION (

SELECT usernames.UID AS `UID` , usernames.Name AS `result` , auth.username AS `playername` , usernames.Uses AS `Uses` , 0 AS `clan_id` , '' AS `clan_abbrev` , '' AS `clan_name` , 0 AS `clan_icon` , stats.kills AS `kills`
FROM `usernames` , `auth` , `stats`
WHERE usernames.Name LIKE '%storm%'
AND auth.UID = usernames.UID
AND auth.clan_id =0
AND stats.UID = auth.UID
)
ORDER BY `Uses` DESC
It works great, but the average query takes around 6s (these tables have several thousand entries in each).

View Replies !

How To Create Index When Doing 'create Table As Select'
Code:
Create mysqlorary table mysql
AS SELECT
Name
FROM
EmployeeList
WHERE
Dept = 'XXX'

I would l ike to create an index on the field Name of the mysqlorary table mysql. I wonder if I can create the index on the fly by adding sth in the above statement.

View Replies !

What Is The Syntax For Joining Multiple Tables?
What is the syntax for joining multiple talbes?

View Replies !

Query Remote Tables Syntax
I have the same database & table on two unix servers that are in a remote location. One table has a couple fewer rows in it than the other. I need to find out which rows they are. I am pretty familier with Sql serve syntax, but cannot find the corresponding syntax for doing this in mysql.

server a has the correct amount of rows (1 million)
server b has 999,500 rows.
I was going to use the following query to find the missing rows:
Select a.pkey
from table a
outer join <server>.<db>.<owner>.table b
where b.pkey is null

this query doesnt seem to work for me. I read the manual web page about setting up obdc connections, but I didnt see how to do that on unix systems.

View Replies !

Large Table
I have a website with over 36,000 registered users. I have a table with 5 million+ records, each of which correspond to a particular user. Obviously some users are tied to a few of these records, others are tied to thousands.I am trying to decide:

-Keep everything in the one table, and allow it to grow infinitely large.
-Create an individual table for each of the 36,000 users and keep only their records in their respective table.

View Replies !

Create Another Field After Create Table
i have create a table from CLI, i would like to add another field after create table.
How to do that ?

View Replies !

Syntax Error On Entering Tables Into Database
As a total newbie, got a problem. I was supplied with a set of tables for a database to enter/import. On doing so, the phpmyadmin returns the following error message:

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1 AUTO_INCREMENT=106' at line 17 .....

View Replies !

MySQL With Large Table.
I am using MySQL for a table which will have 100M+ records, avg length
of records being 130 bytes.

When the number of records reach approx. 25M (and the file size close
to 4GB), the rate of inserts falls drastically from 800 per second to
30-40 per second.

Details:
* MySQL 3.23.58 on Fedora Core 3
* Table has 4 indices.
* I have got rid of 4GB file size problem with MAX_ROWS=1000000000.
* File system : ext3 on single disk. ext3 could create 10G file
without much trouble. So I am convinced that ext3 is not the
bottleneck.
* Tried using InnoDB engine but it also doesn't meet the requirements.

Requirements of database:
* A single table in the database with 100M+ rows, each of size 130
bytes (approx).
* 500-600 inserts per second.
* 200 selects and 200 updates per second. (These statements will
affect only one row)
* 3-5 select statements per minute which can return 10k to 500k
records.
* No foreign keys/ACID transaction requirements.
* Fast recovery in case of crash.

Questions:
* Does MySQL performance drops when the table grows beyond 4G?
* Which are most important server variables which need to fine tuned?
Currently I am using only key_buffer = 512M.

View Replies !

Large Test Table
How do I create a very large table, for testing? Can I download one somewhere? I've tried to search google, but couldn't find anything.

The contents dosn't need to mean anything, so a random function with a
loop or so should do it, but if I could download a large test table
somewhere with "meaning" that would be great.

View Replies !

Breaking Up A Large Table
I'm working with a web-based project management app that stores task information for multiple users in a common 'tasks' table. The table has over 500K rows at this point and is accessed by around 1000 users. The queries are optimized and the table is indexed properly (I think) but as the db grows performance is starting to lag. I'm thinking that the quick fix would be to break up the tasks table into smaller ones by user. So ...
is that a good idea? All of a sudden I'm going to have 1000 more tables. Also the app code will be adding tables on the fly as new users come on board. (The existing tables are all of type MyISAM)

View Replies !

Large Database Table
I am using MYSQL in order to develope a database table that currently has about 20 million records. The table grows on a daily basis and is expected to grow to a size of about 300 million records.I wanted to get some suggestions in order to handle such large database. What kind of indexes are best? When should splitting the table be considered? What are ways of achieving good performance of queries?

View Replies !

Copying A Large Table
I need to copy a large table without locking the database.

So at the moment i have

Insert into newtable select * from oldtable.

This locks the database for 10 seconds which although doesn't sound like a lot of time is 2 much for what i need the database for!.

I have looked at these commands

FOR UPDATE.
LOCK IN SHARE MODE.
SQL_BUFFER_RESULT

But I am not quite sure if any of these actually perform what i would like to do.

The other option is to update the table line by line or in chunks using limit i suppose. But im sure there must be a better way.

View Replies !

Copy Large Table?
I want to copy just part of a very large table from one database to another database. The table contains several million of rows.

So far the only thing I can think of is using MySql dump. However, that will give me all the rows in that table, right? But I just want to grab 1000 rows to do testing.

View Replies !

Large Table Structures
What are your opinions on large table structures? I am building the largest database I have ever built and want to make sure its done right..

We are creating a db for entering information from large forms...

The simple forms I have just been creating as one or two tables as needed..

But now I have a form with the possibility of 100 fields.. many of them blobs...
I am seperating the forms into logical sections... For example, Job Information, Contact Information, etc... Would it be better to store each logical section as a seperate table since the number of fields in the table is so high... or does it even matter at all?

View Replies !

MERGE Vs 1 Large Table
Ok, so I have a bunch of small tables I'm creating based on the data.
Users submit information with a # and a source, and their review of it.
My problem is the #'s between the sources could be identical so I broke the main table up into each table for each source. (only one # per-source so I'm using it as unique so I don't get more than one entry per-source)
I'm curious if MERGING the tables when doing a query will be slower than doing the 1 large table.
My idea for the 1 large table was #####sourceID that way they could be unique still - but it is easier and cleaner to use seperate tables.
So in the end... is merge as fast as 1 large table?

View Replies !

INSERT Syntax For Text File To Load Into Tables?
I have successfully created a number of tables in my database and am looking for an efficient way to load data into the tables. I have the following (successful) command to find the text file and load it into the target table:

"load data local infile 'C:mySQL abledata.txt' into TABLE checks;"

It loads into the 'checks' table successfully, but the data is incorrect. What is the syntax of the INSERT command that I'm using in the text file itself-- assuming that I do use the INSERT command? Here is what I have in the NotePad text file I'm using, which only produces zeroes in the first and third columns:

insert into checks values
('1', 'Ma Bell', '150', 'Have sons next time',
'2', 'Reading R.R.', '245.34', 'Train to Chicago',
'3', 'Ma Bell', '200.32', 'Cellular phone',
'4', 'Local Utilities', '98', 'Gas',
'5', 'Joes Stale $ Dent', '150', 'Grocieries',
'6', 'Cash', '25', 'Wild Night Out',
'7', 'Joans Gas', '25.1', 'Gas');

View Replies !

Create Table Syntax For Large Tables

How do i create tables with sizes larger than 4 GB? Do I need to specify any additional clauses such as heap etc when creating tables?