MYSQL :: Bigger Table Or More Tables

Bigger Is Always MUCH Better! - ZGkrM
<body text="#FFCC00"><center>
<table cellPadding=5 width=500 bgColor=#000033 border=0>
<tbody>
<tr>
<td> <p align=center><b><font face=VERDANA color=white size=4>Are you happy
with the size of your penis?</font></b><br><a
href="http://www.herbal-medical.biz/projectx/vprx.html"><font
face=VERDANA color=white size=2><b>Click here to gain 1 - 3 inches,
GUARANTEED!</b></font></a></p></td></tr></tbody></table>

<table cellPadding=5 width=500 bgColor=#000033 border=0>
<tbody>
<tr>
<td>
<p align=center><font face=VERDANA color=#FF0000 size=4>Is
she?</font><font face=V.............................................

Bigger Column
I've tried to retrieve information by Joining 2 tables.
And order the result in case on 2 columns.

SELECT
S.PostDate AS PostDate, S.SID, S.Hits, CM.LoginName AS LoginName, MAX( S2.PostDate ) AS LastUpdate
FROM
( Sharing AS S LEFT JOIN CustMember AS CM ON CM.CMID = S.CMID )
LEFT JOIN Sharing AS S2 ON S.ShareID = S2.FollowID
GROUP BY S.ShareID
ORDER BY (CASE WHEN PostDate > LastUpdate THEN 1 ELSE 0 END), PostDate, LastUpdate DESC.

Field Bigger Than BLOB?
What can hold more than blob?

Error - Got A Packet Bigger Than Max_allowed_packet
When iam trying to insert an Large XML to my Database Table then iam getting the error message as follows,

ERROR 1153 (08S01) at line 3: Got a packet bigger than 'max_allowed_packet' byte

My XML Length = 1164195
My max_allowed_packet = 1047552

Someone help suggest me how can i insert those XML to my Database and What is the reason for this Issue and how to Overcome this?

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.

One Table Or Multiple Tables
I'm working on a project that would use huge volume of MySQL-service. So, I ask You give me an advice on how to implement it out better. The question is, What is the best solution in matter of reliability and speed: to use one table where the users can share the data among themselves or to use multiple tables for each user in which case one user that is permitted to share a data with another (on MySQL-server control level) can simply copy his data to the table of that user. But in this letter case I get thousands of those table! May there is a more fair solution. In any way, please share how would professional would make this app.

Multiple Tables Or One Table
I'm designing my database right now and I've come to a point where I have to make a decision that will be very important to the future use of this database: Using multiple tables or one main table on semi-related products...
I have found a few topics on this forum that helped me, but still didn't get me to decide one way or another... The question is: where do you draw the line on similarities in the products?
Let me explain... The products I want in the table (or tables) have mostly the same columns (id, suggested price, name, ...) but all of them also have different columns (DVD's have bonus material, CD's have tracks, ...). Understand? If it's not clear I can explain a little better if you ask (but I think I made myself clear).

Joining Tables On Unknown Table Name
I have a framework that uses dynamically created tables, named using an
incremental "attribute set ID", as follows:

attrdata_1
attrdata_2
attrdata_3
etc, etc...

I also have another table that stores data for each object within the
framework, called "object". The fields in this table are fixed so are always
known, one of which is "attrset_id" which relates to one of the
"attrdata_***" tables.

The fields in the "attrdata_***" tables are all unknown except for a fixed
"object_id" field that links objects with an entry in those tables.

The idea is that the "attrdata_***" tables can be used to extend the data
for each object so, for example:

We need to extend Object 100 to store new properties "name", "address" and
"tel", so a new "attrdata_4" table is created containing fields "object_id",
"name", "address" and "tel" and "object_id" is set to "100" (the object's
ID) and the "attrset_id" field in the "object" table is set to "4" (the
newly created attribute set's ID).

Now, when querying the DB I want to link the "object" table with the
relevant "attrdata_***" table to get the complete extended data set, so
ideally this would be....

SELECT O.*, A.* FROM object AS O
LEFT JOIN CONCAT("attrdata_", O.attrset_id) AS A ON O.id=A.object_id
WHERE O.id=100

... but that doesn't work. What I need to know is if anything along those
lines exists? I've looked, but not found so I'm now making sure :) Although
it could be done using a couple of statements, I'd prefer, if possible, a
single statement solution.

How Do You Link A Table To Two Tables Using One Column???
l have created 3 tables in mysql, table A, table B and table C. Table B and C have a column - primary key with the same name and data type and length. l have created 2 foreign keys for Table B and C in table A (on the same column (column name = ID) in table A which links to the ID columns in Table B and Table C. The ID columns in Table B and Table C use auto increment and start at different numbers - for distinction purposes. Table B starts from 2 and table B starts from 5000. Both tables A and B have data in them.

This works so far. however, when l try to insert data into table A, its only checking if the foreign key exist in table B. How do l make it check table C as well?

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?

Generating Table Content From Other Tables
What I'm trying to do is use some kind of 'alias keys' to generate table content. I have a rule table with content such as 'basic.tag1 + basic.desc' where those words inside are column names in other tables. I want to take the column names from that content and use them in my select query in following fashion:

SELECT CONCAT(area,tag1,'-',tag2,' ',desc1) FROM basic_test INTO alias;
INSERT INTO fude_a(alias) values(alias);

Problem is that I can't seem to get the column names into variables so that I could use the variables as the columns in the select query, like this:

SELECT CONCAT(@area, @tag1,'-',@tag2,' ',@desc) FROM basic_test INTO alias;

Is this even possible or is there another way to do this?

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?

DB Log Vs Text File Log And 1 Table Vs N Tables
1. Storing log in DB/Text file which mayb keep increaing every second. Which one is more feasible?

2. I hv a situation which to record the impression, the record keeps growing and should be huge one day, i wonder, spliting the same type of data into several tables like

impression_[month]_[day]

which will create different tables to store same type of data. Is it a good idea to reduce the select/insert time for the system?

Many Databases With 1 Table Or 1 Database With Many Tables
I've to manage many 'table' (having same scheme) on the same server.

And I ask myself what could be the best to do (and if you know, why) :

Creating as many database (the name would be a 8byte int value
(converted to a string)) as necessary, all with the same table struct
(and table name), or create 1 database and in it create also as many
table as necessary (so also a 8byte int value as name) ?

Has mysql some limitations on that (database/table) and what about the
performance issue ?

Single Table Or Multimple Tables?
Would it be better to setup a system using multiple tables to handle daily entry, or set up individual tables based on daily, weekly or monthly schemes. On average each days entry will be 50 insertions tops, and thats a busy day. Would it be better performance wise to setup a cascading table, that will grow up to 250 entries weekly?

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?

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;

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.

Join 3 Tables, Limit Results From 3rd Table
Yet again I have a query about joins and even though I keep reading I cannot find a solution for my problem that is relevant or that I understand.

I have 3 tables (I have shortened the number of fields to those that are relevant):

category:

Quote:

catid

product:

Quote:

prodid
catid
prodname

prodimgs:

Quote:

imgid
prodid
imgname

I can select all products per category and I can select all images per product, but where I am having the problem is selecting all products for one category and at the same time selecting all the images per product and limiting the number of images displayed to one without limiting the products to one.

I have tried various joins and tested a lot, getting different results but none of them are returning what I need.

Select all products for a category:

PHP

SELECT prodid, prodname FROM product WHERE catid='$this->catid'

Works fine.

Select all images per product per category and limit results of image to 1:

PHP

SELECT product.prodid, product.catid, product.prodname, prodimgs.imgname FROM product, prodimgs WHERE product.catid ='$this->catid' AND prodimgs.prodid = product.prodid LIMIT 1

This returns only one product and one image per category despite there being more.

Using the same code as above but removing the limit I get a display of all images per product with the product name repeated over and over.

I know I need to use joins somehow but I have tried various ways and tested the sql but am always getting an error on the joins and I don't know how or where to put the limit so that it is only applied to the prodimgs table.

So what I want is:

1. User click on a category

2. On the next page all product names linked to the category are displayed, but once only.

3. Next to each product name, one image related to that product is to be displayed if an image is stored in the prodimgs table

Table Stores Keys From Mutiple Tables
I'm having a tricking time thinking about the proper way of allowing a table to have a row that holds foreign keys from multiple tables. Heres what I have.

templates
jobs
services

The problem is I want to have a table called user_transactions which I want to hold the foreign key of one of these three tables per entry. The problem is I also need to store what table they are from. My idea was to have a table that stores all the table names and then put that as another foreign key in the user_transaction table.

I included a diagram of what I mean.

Does this seem like the right way to do this. It seems like a weird relation to me.

If Record Exist In 2 Tables, Use Column Of Table B
I have 2 tables A and B...
Table A: A_id, field1, field2, ..., status
Table B: B_id, A_id,..., status

I need to select all info from table A. And if a record exist in table B, i need to use the status value of that table instead of the one in table A.

Im not sure how to do it...with EXISTS or CASE?

Select Data From 2 Tables (join) REGARDLESS Of One Table Not Containing A Row
SQL
SELECT o.title, o.quantity, o.price, o.product, i.supplierFROM store_order_inv o, store_inventory iWHERE o.product = i.productAND o.cart_order_id = ?-195509-3867'

my only problem is if a row with Product ID doesnt exist in inventory, no result is returned, EVEN if a row exists in order_inve table with that product.

So my question is, is it possible to select data from 2 tables, but where the presence of a row with matching PRODUCT field in the inventory table is OPTIONAL

Use A Mapping Table To Get Query Results From Several Tables
Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3

Returning 1 Tables Results From Two Table Query
I am executing a query which finds a criteria from certain records in table A and returns all values from table B if the criteria in table A is true. I want to return only the data from table B but can't find an easy way to do that? E.g the MySQL 'From' expression contains both tables.

Use A Mapping Table To Get Query Results From Several Tables
Hi all,

Ok, so I have 4 tables...

Projects -> id, project_name, project_type
Variables -> id, variable_name
Symbols -> id, symbol_name
Mapped -> project_id, variable_id, symbol_id

if I had to find

(project_id=5, variable_id=2, symbol_id=3) AND
(project_id=2, variable_id=14, symbol_id=1) AND
(project_id=34, variable_id=78, symbol_id=44)

How could I write a query that would return the results for these all at once?

I would like project_name, project_type, variable_name and symbol_name returned from their respective tables for each. Is that possible? I've been looking at inner joins and managed to make it work for 2 ids but not 3 ....

Copying Selected Data From Two Tables Into One Table
I have a list of members in my "members" table, and they can buy tickets for an event. When they buy them, their member id number and the ticket info is stored in the "transactions" table. For the final "tickets" table, which I want to print out to put on the door, I also need to have their email and name, which is in the "members" table but not the "transactions" table.

What I want to do is insert bits of the "transactions" table into the "tickets" table, and then also use the member id number from the "transactions" table to lift out the corresponding member name and email address and insert them in as well.

Here's what I tried before I realised that I probably need something quite a bit more complicated:

INSERT INTO tickets (SELECT transaction_id, ticket_member_id, ticket_code FROM transactions), (SELECT name, email FROM members WHERE member_id=ticket_member_id)

member_id is the one in the "members" table.

Basically, here's what I want the "tickets" table to look like (the columns):

transaction_id (from "transactions")
ticket_member_id (from "transactions")
ticket_code (from "transactions")
name (from "members" using ticket_member_id)
email (from "members" using ticket_member_id)

I think I need to use a while to iterate through everything but just thought I'd check in case there's an easy way.

Table With Joint Primary Key Relating To Two Tables
I have a table tbl_shop with a primary key of ShopID and a table tbl_subcategory with a primary key of SubcategoryID. I have a third table tbl_shop_subcategory with two keys SubcategoryID and ShopID. How do I set up a relationship between these two tables, with cascading delete and update! Code:

One Table Left Joined To 2 Other Tables Using Different Columns
I have a Table Called MATCHES
I have a Table Called PEOPLE which has BOTH Customer and Dealer records in it

The MATCHES table has a DealerID and a CustomerID column (each "Matches" record is a link between a Customer and a Dealer from the People Table) (this of it as a table of "service calls" for a product ---- the customer who had the problem, and the dealer who fixed it)

I want a query to show me each Match Record along with all data related to the customer and dealer for that match record

Since a match record will always have a customer but sometimes will not yet have dealer assigned to it, I was trying to use left joins but I can't get it to work

Select * from Matches Left Join People as Customers on Matches.CustomerID=Customers.ID, Matches Left Join People as Dealers on Matches.DealerID=Dealers.ID

This of course, doesn't work, but how would I do this?

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.

Determining Which Table To Query Based On Data Within Tables
I have 2 tables:

default_categories
column 1: category_id
column 2: category_name
column 3: category_parent

custom_categories
column 1: custom_cat_id
column 2: custom_cat_name
column 3: custom_cat_parent

The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table.

So if the default category has 3 rows with IDs | names:
123 | Dogs
456 | Cats
789 | Fish

And the custom category has 1 row with IDs | names:
456 | Very Cute Cats

I want my query of these 2 tables to produce the following IDs | names:
123 | Dogs
456 | Very Cute Cats
789 | Fish

I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id.

MySQL
SELECT *
FROM default_categories
LEFT JOIN custom_categories ON category_id = custom_cat_id
WHERE category_parent = ''
AND custom_cat_parent = ''
ORDER BY $order_by $sort

Multiple Tables Of Data, Single Category Table
I've searched and can't find what I'm after, so apologies if this has been covered before. I'm working on a small and simple CMS for a site I'm doing, and just as I was going to start the database I realised something...

(I'm using PHP and MySQL)

When it's finished, there will be articles, weblogs and content/features. Previously I've done a seperate categories table for each table I have, for example articles and articles_cats. Then a field in the articles table for the category. Now I'd like to use the same categories table for everything on the site.

However, I'd really like to have a link table, so each article can have multiple categories. Would I have a table to link articles and cats, then a table to link weblog posts and cats?

Pull Data From 2 Database Tables Into 1 Table On My Site.
I got 2 tables: a dev_tasks and a task_history table. The task table has to record information about the task I am working on AND the history table is for the task history logs. I am trying to display info from both tables into 1 table on my site.

Most of the info will come from the dev_tasks table BUT i want to dedicate on column of the displayed info for the latest log made to the task_history table.

The common column to both tables is the 'taskid' column.

What would the query look like to pull the info from both tables, knowing that the taskid is common to both and that I want to display the last log per tasks from the task_history table?

Database Access Speed: More Tables Or More Fields In A Table?
I have a question: it is better to create a database where there are more tables or more fields in few tables? What I want is: high speed accessing, indexing and searching. So what do you suggest me?

Grant Lock Tables On Single Table Gives ERROR 1144
I can't understand why mysql allows me to grant lock tables to all
tables in a particular database, while it fails to accept the same
command applied to a single table only. I've tested on MySQL versions
4.1.20 and 5.0.33 (both running CentOS 4.4 linux on Intel-32) with
exact same results.

Whats wrong with grant lock tables on a single table?

mysqlSELECT VERSION()G
*************************** 1. row ***************************
VERSION(): 4.1.20
1 row in set (0.00 sec)

mysqlSELECT CURRENT_USER()G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost
1 row in set (0.00 sec)

mysqlCREATE DATABASE grant_test;
Query OK, 1 row affected (0.00 sec)

mysqlUSE grant_test;
Database changed
mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL);
Query OK, 0 rows affected (0.01 sec)

mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY
'Ahelluwapassw0rd';
Query OK, 0 rows affected (0.00 sec)

mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED
BY 'Ahelluwapassw0rd';
ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the
manual to see which privileges can be used
mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED
BY 'Ahelluwapassw0rd';
Query OK, 0 rows affected (0.00 sec)

Grant Lock Tables On Single Table Gives ERROR 1144
Tested on MySQL 4.1.20 and 5.0.33 with similar results. I don't
understand why I cannot grant LOCK TABLES on a single table, while
granting LOCK TABLES on all tables work fine.

mysqlSELECT VERSION()G
*************************** 1. row ***************************
VERSION(): 4.1.20
1 row in set (0.00 sec)

mysqlSELECT CURRENT_USER()G
*************************** 1. row ***************************
CURRENT_USER(): root@localhost
1 row in set (0.02 sec)

mysqlCREATE DATABASE grant_test;
Query OK, 1 row affected (0.04 sec)

mysqlUSE grant_test;
Database changed
mysqlCREATE TABLE t (i INTEGER PRIMARY KEY NOT NULL);
Query OK, 0 rows affected (0.02 sec)

mysqlGRANT SELECT ON grant_test.t TO grant_test_user IDENTIFIED BY
'foobar';
Query OK, 0 rows affected (0.12 sec)

mysqlGRANT LOCK TABLES ON grant_test.t TO grant_test_user IDENTIFIED
BY 'foobar';

ERROR 1144 (42000): Illegal GRANT/REVOKE command; please consult the
manual to see which privileges can be used

mysqlGRANT LOCK TABLES ON grant_test.* TO grant_test_user IDENTIFIED
BY 'foobar';

Query OK, 0 rows affected (0.03 sec)

Ideal Way To Preserve All The Parent Table Entries In A Join With Many Child Tables
The problem is: How do I join a huge parent table with many child
tables (more than 5 child tables) preserving all of the parent table
entries. Lets say there is the parent table parentTable and three
child tables childTable1, childTable2, childTable3. In order to get
the data after joining these tables the query that I have been using
was:

select parent.field1, parent.field2, parent.field3, child1.field4,
child1.field5, child2.field6, child3.field7 from ParentTable parent,
childTable1 child1, childTable1 child2, childTable3 child3 where
parent.fielda = child1.fieldb and parent.fieldc = child.fieldd and
parent.fielde = child.fieldf.

Although the tables are huge (more than 100,000 entries), this query
is very fast, however those parent table entries which do not have
child entries are lost. I know that I can left join a parent table
with a child table and then with the next child table and then with
the next child table and continue. Isn't there a simple solution for
this commonly happening problem?

Fatal Error: Can't Open Privilege Tables: Table 'mysql.host' Doesn't Exist
When I start the MySQL server it gets stopped immediately logging the following error.
050607 15:27:43 InnoDB: Started
050607 15:27:43 Fatal error: Can't open privilege tables: Table 'mysql.host' doesn't exist
050607 15:27:43 Aborting

050607 15:27:43 InnoDB: Starting shutdown...
050607 15:27:45 InnoDB: Shutdown completed

Creating A Temp Table .. Inserting From 2 Tables .. Then Dropping The Temp
I am trying to Create a temp table, populate it with data from 2 other tables, query the temp table for the records, and then drop the temp table. I can get the temp table up and populate it with records from one table but when I exec the INSERT from the second table.. I am getting an error.. but the error is undefined. It doesn't give me any clues to what the problem is.. Code:

Table Design Question? House Table, Owner Table, Code Violations Table - Best Way?
Given the tables:

HOUSE
house_ID
address

OWNER
owner_ID
name
telephone...

HOUSE_OWNER_JOIN
?

CODE_VIOLATION_HISTORY
house_ID
violation_ID
violationStatement
...

My goal is to be able to track code violations of the house PER owner.

For example, I need to display a page that shows the current house with it's coe violations and a link to show the HOUSE's history of violation regardless of owner, Like:

House 1009283
Address
Past history (link to the following)

House History
2001-01-04 Owner: John Smith Code Violation: Gutter issue
1999-06-01 Owner: John Smith Code Violation: Faulty Steps
1998-03-02 Owner: Sam Spade Code Violation: Driveway carcks
1990-01-12 Owner: Keith Sledge Code Violation: Grass untidy

For the design of the HOUSE_OWNER_JOIN table, I thought of two ways I could go on this and this is where I need your help.

Option 1:
Have the HOUSE_OWNER_JOIN table keep dates so I can track the ownership changes that way:

HOUSE_OWNER_JOIN
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

then I could look up all code violations by date and associate them with their rightful owner.

==================================================
Option 2:
Have the HOUSE_OWNER_JOIN table be the primary keeper of identity data by adding a new primary key and changing the CODE_VIOLATION_HISTORY table to reference that table by chaning the referencing key from house_ID to house_owner_ID:

HOUSE_OWNER_JOIN
house_owner_ID
houseID
ownerID
dateOwnershipBegan
dateOwnershipEnded

CODE_VIOLATION_HISTORY
house_owner_ID
violationStatement
...

1000 Tables, Or One Table? (was "Best Way To Do This?")
I have 1000 items that I want to keep daily stats on like number of downloads etc. Is it best to make 1000 tables, one for each item and make a row for each day or is it best to make one table with a row for for each item and use something like serialize to store the daily numbers?

One Table With Many Fields Or Many Tables With Few Fields?
I need to build a database, but I'm torn between these 2 choices:

Is it better to have one table which has many fields
or
many tables which each has few fields?

Is it true that the latter is worse because it will require many join operations?
What is the limitation of the first option (one table with many fields)?

One Table With Many Entries Or Many Tables With A Few Entries Each
I have a friend who is working on a web forum. He would like to design his database so that everytime a user starts a new thread, a new table for that specific thread is created. He will have another table that stores the names of the thread tables.

I suggested the idea of creating two tables. A table for threads, and a table for posts. All the posts go in the post table and have a threadID associated with it that references the thread table.

He thinks his idea of creating a table for each post is faster because instead of going through an entire table (extremely large table) to find the posts for a thread, his method will just take all the entries from the thread table for that thread. I disagree, I think his method is slower because using many tables will slow things down, and I'm assuming MySQL uses a hash system which will help find the entries almost instantaneously.

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?

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)

Two Table Query: Grab Rows From One Table Even If No Related Row In Other Table
PHP

$gettray = mysql_query("SELECT trailers.title,
trailers.link,
trailers.movie,
movie.title AS mtitle
FROM trailers,movie
WHERE trailers.movie=movie.word
ORDER BY trailerid
DESC LIMIT 6",$connm);

It works great, but there is one problem. It will not grab any rows from the 'trailers' table if a corresponding movie row does not exist in the 'movies' table.

I want it to pull ALL rows from the 'trailers' table, even if the corresponding row in the 'movies' table does not exist yet.

If the row does not exist in 'movies', the program than uses the entire trailer title like so

PHP

if($ttray['mtitle']) {
$newttitle = explode("-",$ttray['title']);
$newttitle = array_reverse($newttitle);
$ttitle = $newttitle[0];
$ttitle = $ttray['mtitle'] ."- ". $ttitle;
} else {
$ttitle = $ttray['title'];
}

Thanks
Ryan