My query locks up in heidisql and times out on page.

[xjasonx]

SELECT o.orderid,odate,ofirstname,olastname,orderamount,ocountry,other2,o.status, r.oid FROM orders o INNER JOIN oitems oi ON o.orderid = oi.orderid INNER JOIN products p ON oi.catalogid = p.catalogid LEFT JOIN refund r ON o.orderid = r.oid WHERE p.ccode = 'code' ORDER BY odate DESC

 

Can someone give me a better way to write this query? I've been battling with it for a while. Thank you.

[jskywalker]

SELECT 
o.orderid,odate,ofirstname,olastname,orderamount,ocountry,other2,o.status, r.oid 
FROM 
orders o 
INNER JOIN oitems oi ON o.orderid = oi.orderid 
INNER JOIN products p ON oi.catalogid = p.catalogid 
LEFT JOIN refund r ON o.orderid = r.oid 
WHERE 
p.ccode = 'code' ORDER BY odate DESC

 

 

 

above is more readablie on the forums.....

 

i think you may miss some indexes....

 

can you give the outpt of:

1) EXPLAIN <your query>

2) SHOW CREATE TABLE  <tablename>    (for all tables involved in your query)

[xjasonx]

Sorry about the format.

 

This query is used in a search. "oitems" contains products from orders. The LEFT JOIN was added to determine which orders have a refund. I would just add another column called refund in the orders table to determine that, but that is not an option.

 

The query worked before the LEFT JOIN was added. If I replace it with a RIGHT JOIN, it doesn't break, but of course it doesn't return what I want it to. The orders and oitems table is humongous so I can see how adding refunds to it could cause some problems.

[jskywalker]

sure, i made some typo's in my previous post, but that does not give you the right to ignore this part of that post:

 

can you give the outpt of:

1) EXPLAIN <your query>

2) SHOW CREATE TABLE  <tablename>    (for all tables involved in your query)

[xjasonx]

I didn't. I was at home so I was unable to show the table.

 

oitems

CREATE TABLE `oitems` (
  `orderitemid` int(11) NOT NULL AUTO_INCREMENT,
  `orderid` int(11) DEFAULT '0',
  `catalogid` int(11) DEFAULT '0',
  `numitems` double DEFAULT '0',
  `itemname` longtext COLLATE utf8_unicode_ci,
  `unitprice` double DEFAULT '0',
  `dualprice` double DEFAULT '0',
  `supplierid` int(11) DEFAULT '0',
  `address` longtext COLLATE utf8_unicode_ci,
  `features` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pinnumber` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `customerimage` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`orderitemid`),
  KEY `ix_orderid` (`orderid`)
) ENGINE=InnoDB AUTO_INCREMENT=3309624 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

products

CREATE TABLE `products` (
  `catalogid` int(11) NOT NULL AUTO_INCREMENT,
  `ccode` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cdescription` longtext COLLATE utf8_unicode_ci,
  `cprice` double DEFAULT '0',
  `ccategory` int(11) DEFAULT '0',
  `cdescurl` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `features` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cimageurl` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cstock` int(11) DEFAULT '0',
  `weight` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mfg` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `subcategoryid` int(11) DEFAULT '0',
  `retailprice` double NOT NULL DEFAULT '0',
  `specialoffer` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `category` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `buttonimage` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cdateavailable` date DEFAULT NULL,
  `allowusertext` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother4` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother5` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `userid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `template` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extendedimage` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extendeddesc` longtext COLLATE utf8_unicode_ci,
  `selectlist` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `level3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `level4` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `level5` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `minimumquantity` int(11) DEFAULT '0',
  `supplierid` int(11) DEFAULT '0',
  `crossselling` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hide` tinyint(4) DEFAULT '0',
  `productmatch` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `customermatch` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `orderattachment` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `orderdownload` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `groupfordiscount` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `clanguage` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `points` int(11) DEFAULT NULL,
  `pointstobuy` int(11) DEFAULT NULL,
  `price2` double DEFAULT NULL,
  `price3` double DEFAULT NULL,
  `billprice` double DEFAULT NULL,
  `billinstallments` int(11) DEFAULT NULL,
  `billinstallmenttype` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `billinterval` int(11) DEFAULT NULL,
  `maximumquantity` int(11) DEFAULT NULL,
  `frontpage` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `inventoryproducts` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `taxfree` tinyint(4) DEFAULT NULL,
  `freeshipping` tinyint(4) DEFAULT NULL,
  `highercatalogid` int(11) DEFAULT NULL,
  `spdisplaytype` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hassubproduct` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `impressions` int(11) DEFAULT NULL,
  `cdateadded` date DEFAULT NULL,
  `cdateupdated` date DEFAULT NULL,
  `templatelisting` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage1` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage2` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage3` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage4` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage5` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `featuredflag` tinyint(4) DEFAULT NULL,
  `customerimage` tinyint(4) DEFAULT NULL,
  `rmadays` int(11) DEFAULT NULL,
  `callforprice` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`catalogid`),
  KEY `ix_products_1` (`ccategory`)
) ENGINE=InnoDB AUTO_INCREMENT=3798 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

orders

CREATE TABLE `orders` (
  `orderid` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `ocustomerid` int(11) DEFAULT '0',
  `odate` date DEFAULT NULL,
  `orderamount` double DEFAULT '0',
  `ofirstname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `olastname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oemail` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oaddress` text COLLATE utf8_unicode_ci,
  `ocity` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `opostcode` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ostate` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocountry` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ophone` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ofax` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocompany` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocardtype` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocardsubtype` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocardno` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocardname` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocardexpires` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ocardaddress` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oprocessed` tinyint(4) NOT NULL DEFAULT '0',
  `ocomment` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `otax` double DEFAULT '0',
  `opromisedshipdate` date DEFAULT NULL,
  `oshippeddate` date DEFAULT NULL,
  `oshipmethod` int(11) DEFAULT '0',
  `oshipcost` double DEFAULT '0',
  `oshipname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipcompany` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipemail` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipmethodtype` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipaddress` text COLLATE utf8_unicode_ci,
  `oshiptown` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipzip` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipstate` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipcountry` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `opaymethod` int(11) DEFAULT '0',
  `other1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `other2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `otime` time DEFAULT NULL,
  `oauthorization` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oerrors` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `odiscount` double NOT NULL DEFAULT '0',
  `ostatus` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oaffid` int(11) DEFAULT '0',
  `odualtotal` double DEFAULT '0',
  `odualtaxes` double DEFAULT '0',
  `odualshipping` double DEFAULT '0',
  `odualdiscount` double DEFAULT '0',
  `ohandling` double DEFAULT '0',
  `odualhandling` double DEFAULT '0',
  `coupon` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `coupondiscount` double NOT NULL DEFAULT '0',
  `coupondiscountdual` double DEFAULT '0',
  `giftcertificate` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `giftamountused` double NOT NULL DEFAULT '0',
  `giftamountuseddual` double DEFAULT '0',
  `canceled` tinyint(4) DEFAULT '0',
  `ipaddress` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hackeryesno` tinyint(4) DEFAULT NULL,
  `customercancel` tinyint(4) DEFAULT NULL,
  `opoints` int(11) DEFAULT NULL,
  `vatnumber` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `commission` double DEFAULT NULL,
  `opending` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `shipmessage` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `hearaboutus` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oaddress2` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oshipaddress2` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deliverydate` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deliverytime` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `producttotal` double DEFAULT NULL,
  `oinsurancemethodtype` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oinsurancecost` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `oinsurancemethod` float DEFAULT NULL,
  `odualinsurance` float DEFAULT NULL,
  `upstrackno` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `opst` double DEFAULT NULL,
  `odualpst` double DEFAULT NULL,
  `otermsagreed` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  `affdatepaid` date DEFAULT NULL,
  `affpaid` tinyint(4) DEFAULT NULL,
  `ocurrency` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'USD',
  `ocurrencyrate` double NOT NULL DEFAULT '1',
  `whygl` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glcardtype` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glavs` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glcvv2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glauthcode` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glvoid` tinyint(4) NOT NULL DEFAULT '0',
  `glpaypalemail` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glptime` timestamp NULL DEFAULT NULL,
  `glpby` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glamountpaid` double DEFAULT NULL,
  `glcid` varchar(5) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glxr` double DEFAULT NULL,
  `gllang` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `glvendorid` varchar(10) COLLATE utf8_unicode_ci NOT NULL DEFAULT '34137',
  `glcalculated` tinyint(3) unsigned DEFAULT NULL,
  `status` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`orderid`),
  KEY `ocustomerid` (`ocustomerid`),
  KEY `oprocessed` (`oprocessed`),
  KEY `ocardtype` (`ocardtype`)
) ENGINE=InnoDB AUTO_INCREMENT=3284472 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

 

refund

CREATE TABLE `refund` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `oid` int(11) NOT NULL,
  `ttype` varchar(25) NOT NULL,
  `ccnum` int(4) NOT NULL,
  `amount` float NOT NULL,
  `cctype` varchar(75) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6578 DEFAULT CHARSET=utf8

 

 

[luca200]

I think you should add indexes on:

- oid on refund table

- catalogid on oitems table

 

[xjasonx]

I agree. I think that would at least minimize the problem. I don't have alter table permissions, so I'll have to wait on that.

[Mchl]

`address` longtext COLLATE utf8_unicode_ci,

In what country addresses are so long, that they require up to 4GB of storage?

 

  `unitprice` double DEFAULT '0',
  `dualprice` double DEFAULT '0',

storing monetary values as DOUBLE is asking for trouble. Use DECIMAL instead. And I mean it for all monetary column in all your tables.

 

`weight` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,

A VARCHAR? How you store it? 'Five tons' ? - Numeric values, should be in numeric datatypes.

 

  `pother1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother4` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pother5` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

 

  `extraimage1` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage2` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage3` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage4` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `extraimage5` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,

This indicates broken normalisation. Perhaps move to another table?

 

  `userid` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,

 

You sure it's not INTEGER?

 

  `ocardno` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,

 

You're not storing unencrypted credit card numbers in your database, are you?

 

 

In general your `orders` table is bloated. It's very likely that it could be normalised further.

 

Also, apart from INTEGER (INT) and TINYINT, there are MEDIUMINT and SMALLINT integer types, which you could use in many places instead of INTEGER.

 

 

[xjasonx]

Thank you luca200. That fixed heidisql from locking up.

 

Those are some great points Mchl. This is actually a commercial asp (hate asp) cart and the table comes that way (for the most part).

 

We only store  the last 4 digits of the credit card number, so that should not be a varchar(100).

 

We don't need addresses to be longtext.

 

weight was probably designed that way so the user can put ints or strings in it.

 

I'm looking forward to going to work tomorrow and improving on some of these fields.