Re: very slow select with join

Michael Stassen Sun, 30 Jan 2005 12:11:25 -0800


Pavel Novák wrote:

<snip>

yea, but I use a lot of indexes and I don't know how to set it better.
Bellow there are create table info so could you tell me what do you
think would be bad, please?


To begin with, you have redundant indexes.  Unnecessary indexes slow you 
down.

CREATE TABLE `order_acl` (
  `objid` bigint(20) unsigned NOT NULL default '0',
  `gid` bigint(20) unsigned NOT NULL default '0',
  PRIMARY KEY  (`objid`,`gid`),
  KEY `gid` (`gid`),
  KEY `gid_2` (`gid`,`objid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

A multi-column index works for any leftmost prefix. In other words, index `gid_2` works as an index on column gid, so you don't need index `gid`.

  ALTER TABLE order_acl DROP INDEX gid;

CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL default '0',
  `orderid` bigint(20) unsigned NOT NULL default '0',
  `serialid` tinyint(3) unsigned NOT NULL default '0',
  `cancel` tinyint(3) unsigned NOT NULL default '0',
  `authorized` tinyint(4) unsigned NOT NULL default '0',
  `createdby` bigint(20) unsigned NOT NULL default '0',
  `createdon` bigint(20) unsigned NOT NULL default '0',
  `changedby` bigint(20) unsigned NOT NULL default '0',
  `changedon` bigint(20) unsigned NOT NULL default '0',
  `brandid` bigint(20) unsigned NOT NULL default '0',
  `productid` bigint(20) unsigned NOT NULL default '0',
  `producttype` varchar(32) NOT NULL default '0',
  `warrantyid` bigint(20) unsigned NOT NULL default '0',
  `customerid` bigint(20) unsigned NOT NULL default '0',
  `scheduleid` bigint(20) unsigned NOT NULL default '0',
  `pnc` varchar(32) NOT NULL default '',
  `seno` varchar(32) NOT NULL default '',
  `note` text NOT NULL,
  `descr` text NOT NULL,
  `workdescr` text NOT NULL,
  `worktime` int(10) unsigned NOT NULL default '0',
  `workprice` int(10) unsigned NOT NULL default '0',
  `materialprice` int(10) unsigned NOT NULL default '0',
  `transprice` int(10) unsigned NOT NULL default '0',
  `papernumber` int(10) unsigned NOT NULL default '0',
  `builtin` tinyint(3) unsigned NOT NULL default '0',
  `dobuiltin` tinyint(3) unsigned NOT NULL default '0',
  `completed` tinyint(3) unsigned NOT NULL default '0',
  `express` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `brandid` (`brandid`),
  KEY `productid` (`productid`),
  KEY `customerid` (`customerid`),
  KEY `orderid` (`orderid`),
  KEY `createdby` (`createdby`,`changedby`),
  KEY `completed` (`completed`,`express`),
  KEY `orders`
(`createdby`,`changedby`,`customerid`,`productid`,`brandid`,`scheduleid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

Same thing here. Index `orders` makes index `createdby` unnecessary. On the other hand, if index `orders` has more columns in it than are actually used to choose rows in any single query, it is too big. The trick is to match indexes to what is actually needed by your typical queries. Without knowing what queries are typical for you, I really can't comment further on whether your indexes are helping or not.

Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Re: very slow select with join

Reply via email to