MYSQL :: SQL Tables

Maximum Allowed Tables/db, Fields/tables, Records/table
1)tables that can be created in a MySql database.
2)fields that can be created in a MySql table.
3)records allowed in a MySql table and in a MySql database.
4) allowed joins in a MySql table/database.

Cannot Alias Locked Tables / Join To Unlocked Tables ?
If you manually issue a table lock then query that table, aliasing the table generates an error. If you try to join the table to another table that is not locked, you will receive an error. What is the reason for this?

Multiple Smaller Tables V.s. Fewer Bigger Tables
What is the best way to store data in a database? Multiple smaller tables (which means many Inner/Left Joins when fetching the information) or fewer bigger tables (which means few or no Inner/Left Joins when fetching information).

InnoDB Tables And MyISAM Tables In ONE Database?
My application demands some advantages from InnoDB and some from MyISAM.

Is it attainable to have both InnoDB tables and MyISAM tables in ONE database?
Or else, getting views from two databases, one from InnoDB and the other from MyISAM?

If not possible, any other approaches will do the job?

How To Get The SUM Value From Two Tables? Join Tables? Subquery?
Hi,
I have two tables:

table A

Id. | id_result | value
1 | 1| 10
2 | 1| 11
3 | 2| 7
4 | 2| 13

table B

Id. | id_result | value
1 | 1| 4
2 | 1| 1
3 | 2| 5
4 | 2| 6

How can I get sum of unique keys from table A and B (id_result) like this?:

id_result | sum_table_A | sum_table_B
1 | 21| 5
2 | 20| 11

I can do it with UNION or 2 separate SQL statement, but how to make it in 1 query or using subquery?

100 Databases, 4 Tables Each, Or 1 Database, 400 Tables
I was able to build, tweak and do whatever necessary to establish a rather different project but am worried that it may sooner or later crash due to idiotic programming / database usage.

In essense I took 1 database with 4 tables and duplicate it 100 times BUT because I need to connect to the different tables within the php script and am not experienced enough to created nested database connections, I duplicate the 400 tables within the same database.

What I want to know from a MySql perspective, would it make any difference if the script "play around" with 1 database connection and 400 tables or would 100 databases with 4 tables each be better? (Maximum database queries to only 17 tables at a time)

More Tables, Or Huge Tables?
i'm opening a web-service where people can open their own sites.
i have about 18 tables for a website to be put on the air.

i wonder what's better for my users:
one huge table with a lot of rows, or opening 18 new tables in a database in the format:
0_users
0_images
1_users
1_images
2_users
2_images

what's better performance wise? for 1000 users? for 10000 users? 100k users?
i assume that in about 20k users i'll have to get a standalone db server..

How Can I Tell If My Tables Are MYISAM Tables Or What?
Is there a MySQL commad to determine what table type existing tables in a
database are?

Small Tables Or A Few Big Tables
My Web site is going to use a MySQL database extensively throughout the
Web site. It is a content management system type Web site with lots of
users and written articles and other things. With my database I'm
trying to figure out if I should create big tables that have a lot of
rows in them or if I should create a lot of tables that are very
specific to its data and use a lot of table joins in my select
statements. I'm mostly concerned about performance.

I'm thinking that if I make a lot of tables, then select statements I
do will be really fast because my tables won't contain very many rows.
But I don't know if this is true or not or if it matters for
performance reasons or not.

In my Web site I have a lot of different kinds of data that could put
in many different tables. But, without data redundancy and without
using extra storage space, I can still make a few big tables, and make
that work. I just don't know which way is best for performance --
speed, I want speed. Anybody know what I should do or know a book I
should read or a Web site to go to?

Tables On ISP
I am a newbie. I recently had the web developers for my company create a form online for one of my vendors that uses MySQL as the database. I have been unsuccessful at using the command line to connect to the tables. Do i need the mysql service on the computer that i am using to connect to my web server? What exactly do i need to connect to the tables?

Many Tables In 1 DB Vs. Few Tables In Many DBs
How much of a performance hit will I have if I have 1 database with 300 tables, versus 30 databases with 10 tables each? (the single database would have about 30x the traffic of any single database). I don't expect the tables themselves to be very long at all, perhaps a couple hundred records max.

Also, this will all run on the same server installation either way, so with 30 tables I don't get 30 MySQL servers. The traffic isn't going to be super heavy at all.

So, the question is, is it really worth setting up 30 DBs or should I just drop everything into one and keep it simple?

Get Max() Value Between Two Tables
What is the best way to get the maximum value of a "like" field in two tables?(I.e.

Table1 - Field1 - Values: 0002, 0003, 0005
Table2 - Field1 - Values: 0001, 0005 )

Result should be: 0005

Too Many Tables - Bad?
I have a database with 138 tables. Is having too many tables bad? Can it slow things down?

Tables In Use
Everytime I try a repair with my Web Host Manager my whole site freezes up.

I have one table that is "in use" a sessions table.

I can use putty and get to the
>Mysql prompt but I am lost from there...

Is there a way to repair or fix this? I have tried using phpmyadmin and cpanel and neither work, they just time out.

About Tables
I have some tables, but forgot to save the folder of these table. I want to know how i can open my tables and find the database name. i konw this is a silly question. but i really can't find the anwser.

Dba Tables ?
In Oracle database there are tables for administration purpuse like dba_users, dba_tables, etc. There are any tables like those in mysql?

I want to see for example the structure of a table without using php admin or other graphical tool.

Big Tables
I have a big inno table (about 20 GB). Is it posible to divide the table into several files?

Tables
I have done the SHOW TABLE STATUS FROM database;

Now I just watnt to list the names of all tables, Data_length, and Data_Free. Is there away to do this?

Two Tables At Once
I have two tables
Contact
ID = autonumber
NameID = Id1 from Master_Roll tabel
ContactDate = contact Date
details = Deatails of this contact

Master_Roll
ID1 = Autonumber
Surname = Surname
First NAme = First Name
DOB
SEX
PHONE
ADDRESS
EMAIL

I want to query the contact Table and the Master_roll Details table, I also want to add and update both tables.
Which is the best way to do this?

Sum From 2 Tables
I cannot seem to get the query to work.

I have 3 tables. A, B and C. A.id = B.id and A.id = C.id

I want to get A.* , sum(B.value), sum(C.value) for all records in A.

I am trying this but I can't seem to get it to work.

Select A.id, A.name, sum(B.value), sum(C.value) from A, B, C where A.id = B.id and A.id = C.id group by A.id;

When I run the query it just sits there.

Is this query correct? How can I achieve this, if my syntax is wrong.

How Many Tables
I am currently trying to speed up a database that is performing extremely slowly (1 hour or more for the average selection, which I would like to take a few seconds in an ideal world). The biggest table in the database currently has 50 million lines (around 30GiB for the whole database). Each line is made up of a source id (a number between 1 and 37) a datetime column, a user column, a port column (of which there can be up to 65535*2 ports per hour per user per source id). I'm currently using innodb and MySQL server 4.1.14.

Now the source column obviously has a very low uniqueness, and importantly, no read request will EVER request more than one source id. I am planning to summarize the information older than 2 months into two other tables (daily data and monthly data when it gets old enough) and I am thinking of splitting up the information into separate tables for each source id - however, this is all in preparation for the database to grow roughly 30 times its current size (1000 source ids) - this would mean 3000 tables.... is that a bit extreme? Is there a better solution?

Look Up Between Two Tables
I'm using mysqlcc and mysql server 4.1

I have two tables: tbl01 and tbl02

tbl01 needs to look up data from tbl02 and store it.

Then when I change the data in tbl02 it will automatically update the data in tbl01.

I have been trying to do a look up as in Access but canâ€™t find how to.

One Or Many Tables
What is a better method: creating one large table with a category field or several smaller table
distinguished by the category?
The tables have identical structures. The records are retrieved only form one category at the
time.

Set Up My Tables
I need to set up some tables for a bookmarking module that I am making for a CMS. The data contained will be:

userID
catID
catNAME
bmName
bmURL

This will all have to be grouped around the userID as that will vary depending on who is logged in at any given time and I will be running queries like

select all where username = 1 and catID = 10 , etc, etc

I will also need to be able to insert new categorys (catID) that will reamin unique to each user.Now I would like to know what the best way would be to set up the tables for this - I could do this by trial and error, but I would like to hear an experts advice/opinion.

Two Tables
there are two kinds of users

- ordinary users
- special users

Both kinds have the same fields like login,password,email etc. Special users have few more like name,surname,specialisation etc.

Should I use one table ?

user_profile:

id
login
password
email
name - NULL
surname - NULL

Big 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?

Tables
I just did an update to mysql 5 from 4. Now it seems a bunch of my tables are corrupt. In phpMyAdmin they are comming back as "in use".
When I try to run a query I get this:

#1017 - Can't find file: 'tablename' (errno: 2)

it seems to only be affecting the really old websites/tables. Please help.

Sql Tables
i cant seem to fix.I made the two sql tables and the php works but it is not working with my sql and this is what it says.

Warning: mysql_connect(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /vhost/vhost3/o/n/e/oneeyedbeaver.co.uk/www/config.php on line 10
cannot connect to server .What am I doing wrong? I have php forms all set up and they are all ok.Any help would be very appreciated many thanks. Im also sorry about this if it is in the wrong section.

Too Many Tables
I recently started on a project in which medical data is going to be stored in a MySQL DB. I had never really thought about it this way but because the data is so private and sensitive, the insurace/patients/doctors request that the information be stored seperatly for each user. Right now that would mean a seperate table for each user which could easily add up thousands of tables. I am not by any means a DB expert but when I googled it I found varying opinions. Some people say that 150 tables is alot and others say they have worked with DB's that have 15,000. I just want to know if there is any significant performance decrease as more tables are added.

Certain Tables
In my phpbb forums database, I have woken up to find out that several (7 I think) tables have disappeared mysteriously.They are phpbb_: config ; confirm ; disallow ; forums ; forums_prune ; posts, and I haven't found the other one for some reason, though I haven't checked them all thoroughly, as I have quite a few for various mods.
Anyhow, I thought the tables crashed or there was some kind of a dodgy error with mysql, but I don't know how to find anything like that out. There's nothing to suggest that in the logs.
I wish to rule out the possibility of hacking, at least for now, because the password for the database exists only in the config.php file of the script, and it is a long string of random charters.
Same thing with the root password, but it's even more complex, and is only stored in my brain.
Regardless, is there any possible explanation for this, and if so, is there any way to recover the tables?I've had a look in the directory where they are stored, and the tables which have disappeared are simply not there, which I find very worrying.

Two Tables
I have the following tables. (Please see png image)
I want to keep my site members contacts (other members) in a different table and link them.So in the table contacts I will have two columns membersID and membersID (named contactID). Is this bad design since I cant think of any other way to store members contacts.

Two Tables
I want to get the records from table design AND from the table project.
I tried :
SELECT * FROM design,project WHERE design.headline = 'BOOKLET' AND project.headline = 'BOOKLET'
but I only get the records from the table project and I get them 4 times

Sum For 2 Tables
Is that possible to total up from 2 different column sum ?
example
table A sum(c) and table B sum(d), and now i would like total up the sum(c) and sum(d).
For you info, i'm using 'uinion all' to display 2 'select' results .

1-many Tables
The code below is used to sum all product prices stored in an orderline table. A sale can have more than one orderline, which sums fine, but the delivery is not summing correctly (delivery is summed for every orderline, not every sale).

Is there anyway to get around this problem?

SELECT
SUM(orderline.oln_prod_price) AS price
, SUM(sales.prod_delivery) AS delivery

Changes To Tables
I'm working on an application that someone else wrote (using PHP and MySQL).
There are LOTS of queries that make changes to different tables with joins and it's really hard to track down what's been done to the data after running the code.
I've been trying to check some of the changes with mysql_affected_rows (for updates) and I've tried that function every way I can think of and I can't get that function to work.
I resorted to using a counter and just counting the number of times that a while loop runs to sort of get an idea of how many records _might_ have been touched.
That has (obviously) turned out to be unreliable.
Does anyone know of a tool (or method) to show changes to a mysql DB after running a query? I looked at mysql diff, but it just looks at structure changes. I need to know how many records were touched when this code was run and I've been working on this for quite sometime and I'm not getting anywhere

Two Tables
I have 2 tables that are maintained independently but contain several columns containing data with the same field names.
How do I join one table on top of the other so that I can select from both, As if it were temporarily one table?

Too Many Tables
Does anyone know when a database has too many tables?
I have a database with 44 tables, they are all very different, and I am thinking about moving more over to this DB so I don't need to constantly switch DB's within my pages.
So, how many tables are too many tables?

Two Tables
I need to store data about cars. As you can guess there are many fields like make, model, color, type, price, mielage ...etc Those are ussualy varchar or int type.
There is also somewhat large number of "extras" such as ABS, power windows, alloy rims, sunroof, ESP ...etc. Those are checkboxes (BOOL type).
Is it better (from performance point of view) to keep all that in one table - or have two that are connected by id?
Also what would be good candidates for indexes - I guess numeric fields that are ussualy going to be used in queries, like price?

2 Tables
2 Tables, the first table (COMPANY) holds data for companies and its primary key is C_ID) The second table (ACTIVITY) which holds activities and has a primary key of A_I*D and a foreign key named C_NUMBER which references the other tables primary key (C_ID). I'm trying to perform a search by company name (Which I have working well) I'm also trying to search by a certain activity date which should return all the activities scheduled on that date, along with their coresponding companies. Can you help me develop a sql query that would return the activity date (A_DATE) along with its C_NUMBER so I can put this info into a tabular format, so it could be read on the next page?

Tables From 5.0.27
I recently upgraded to 5.0.27, only to find that it is not nearly as stable as the previous version I was running, 4.1.14. I've had constant problems since upgrading, and would like to move back to 4.1.14.
However, apparently tables created with 5 are unreadable by 4. Is there a way to convert the tables that were created under 5 to be compatible with 4?

Should I Merge Those Tables?
I'm in a bit of dilemma right now. I have a mysql table called `cars` and one called `maintenance`.
The table `maintenance` is related to `cars`. Now I want that in the table `maintenance` are always the same rows as in the table `cars`. Not exactly the same rows, but 1 column of `maintenance` is related to 1 column of `cars`.

I could also merge those tables, but then the table `cars` would have so many columns. So I thought two separated tables would be more synoptic. What should I do?

I already tried to relate those columns with FK-relations with InnoDB, but I don't think that's really the best way.

Setting Up Tables
I'm having trouble figuring out the table setup and normalization I'll need to accomplish my goal.

Basically on my website I want students to register and from there they can log in and fill out what college they go to and create a list of courses they are taking and add/delete assignments for each course, and add grades they receive for each course.

I need help determining what tables I'll need and how I want to link them.

Let's say a STUDENT adds 6 COURSES and then 10 assignments in each course along with 8 grades he has received in the past week (to calculate GPA). How would I go about setting this up?

Where Are Tables Stored?
Hi,

I had MySQL 3(?) installed using IBServer (an all-in-one installer).
I had some databases and tables.
I then uninstalled IBServer and installed a newer version of MySQL.
How can I get my old databases and tables into the new MySQL?
Are they stored as files somewhere? With what extension?

How To Join Tables?
I would like to select info from just two tables. I've tried two seperate select calls but it gives me some of the same info from each table twice.

Can someone please post a "join for dummies" example on how I would do this.

I've tried using "Union" but I'm still just a newbie and was even more confused.

Select From 2 Or More Tables
I need to run a select quer against 2 or more tables and return rows with data from all tables, but if data does not exist in one table, no data is returned from the other tables.

How can I ensure that a row is returned even if one table does not contain data corresponding to the other tables?

For example:
lets say I got two tables: "users" and "user_groups". "users" contains the columns; id and name, and the "user_groups" contains the columns user_id and group_name.

I run a select query as follows:
SELECT users.name, user_groups.group_name FROM users, user_groups WHERE users.id=user_groups.user_id
This works great if a user_group exists for every user. But if a user_group does not exist, then the user's name is not returned at all, even though it does exist.

So how can I make it return the data even if the user group does not exist??

Joining 3 Tables
I am using MySQL 4.0.25.

I have the following tables:

Gallery
gallery_id
gallery_name
gallery_desc
feature_image
archived

Album
album_id
album_name
gallery_id
feature_image
archived

Image
image_id
album_id
sml_url
image_title
image_orientation
...

I am attempting to select all of the galleries - I have the following SQL so far:

SELECT count(a.album_id) as 'album_count', g.gallery_id, g.gallery_name, g.gallery_desc, i.album_id, i.image_title, i.small_url, i.num_views FROM gallery g LEFT OUTER JOIN album a ON g.gallery_id = a.gallery_id AND a.archived = 0 LEFT OUTER JOIN image i ON i.small_url = g.feature_image WHERE g.archived = 0 GROUP BY g.gallery_id, g.gallery_name, g.gallery_desc, i.album_id, i.image_title, i.small_url, i.num_views ORDER by g.gallery_name ASC

The problem with my join is that it seems to be getting multiple records for each gallery - ie I have a gallery called Travel containing 2 albums - Spain and Scotland......The above query returns 2 gallery records...

Transaction And Two Tables
a little confused about transactionts on Mysql. I have such situation:
Two different tables, I need to add many rows to the first table and after those rows are added I need to add one row to the second table...

My question is how do I use transaction on two tables at the same time (no problems when dealing with just one table), because it's needed to success on all queries to both tables, if one fails, then rollback to starting point.

Efficient Tables.
I am starting out on a project where I need to store GPS information. The data consists largely of a series of "Points" each consisting of a longitude,latitude and elevation.

On a typical "route" there could be hundreds of points.

My question is how can I efficiently store this information. It does not sound sensible to normalise this and add hundreds of rows to a table for each "route".

Sample data is along the lines of :

- <trkpt lat="54.016942977905273" lon="-1.4903640747070313">
<ele>82.330078125</ele>

<time>2006-09-03T07:35:41Z</time>

</trkpt>

- <trkpt lat="54.016938870772719" lon="-1.490332055836916">
<ele>0</ele>

<time>2006-09-03T07:35:42Z</time>

</trkpt>