SQL creates high server load?

[crewson548]

Please suggest me with some more example with this load problems>
can any one tell me the peak load point for an server?

[markman641]

Originally Posted by Old Pedant

As I see it you need these tables:

CREATE TABLE Categories (
    catid INT AUTO_INCREMENT PRIMARY KEY,
    catname VARCHAR(100)
);

CREATE TABLE Countries (
    coid INT AUTO_INCREMENT PRIMARY KEY,
    coname VARCHAR(100)
);

CREATE TABLE Customers (
    custid INT AUTO_INCREMENT PRIMARY KEY,
    coid INT FOREIGN KEY REFERENCES countries(coid),
    custname VARCHAR(100))
);

CREATE TABLE Offers (
    offerid INT AUTO_INCREMENT PRIMARY KEY
    catid INT FOREIGN KEY REFERENCES categories(catid),
    offerdetails .... one or more fields to describe the offer ...
);

CREATE TABLE OffersByCountry (
    offerid INT FOREIGN KEY REFERENCES offers(offerid),
    coid INT FOREIGN KEY REFERENCES countries(coid)
);

CREATE TABLE CompletedOffers (
    custid INT FOREIGN KEY REFERENCES custormers(custid),
    offerid INT FOREIGN KEY REFERENCES offers(offerid)
);

The foreign key reference in magenta there may not be necessary, but it is likely a good idea.

The syntax there for the foreign keys isn't quite correct for MySQL, but it's a compact form that easy to read for a post like this.

SQL query:

CREATE TABLE Customers(

custid INT AUTO_INCREMENT PRIMARY KEY ,
coid INT FOREIGN KEY REFERENCES countries( coid ) ,
custname VARCHAR( 100 )
));

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 'FOREIGN KEY REFERENCES countries(coid),
custname VARCHAR(100))
)' at line 3

The same error appears for all tables after that too.

[Old Pedant]

Look at what I wrote:

The syntax there for the foreign keys isn't quite correct for MySQL, but it's a compact form that easy to read for a post like this.

The correct syntax is:

CREATE TABLE Customers (
    custid INT AUTO_INCREMENT PRIMARY KEY ,
    coid INT,
    custname VARCHAR( 100 ),
    CONSTRAINT FOREIGN KEY fk_customers_coid ( coid ) REFERENCES countries( coid ) 
 );

You don't have to give the foreign key a name (the part in red there). MySQL will create a name for you if you don't, and you can then find the created name by doing

SHOW CREATE TABLE Customers;

You can see why I prefer the more compact version I use when posting. It's clearer what the intent is, even if the syntax isn't quite right.

[Old Pedant]

Here's an example of the name MySQL generates if you omit it. This is from SHOW CREATE TABLE:

CREATE TABLE `customers` (
  `custid` int(11) NOT NULL AUTO_INCREMENT,
  `coid` int(11) DEFAULT NULL,
  `custname` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`custid`),
  KEY `coid` (`coid`),
  CONSTRAINT `customers_ibfk_1` FOREIGN KEY (`coid`) REFERENCES `countries` (`coid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1