List:General Discussion« Previous MessageNext Message »
From:Richard Dice Date:March 11 1999 4:38am
Subject:Humbly looking for help in optimizing a query
View as plain text  
Hello, MySQL-ers...

I was hoping that some kind soul out there could help me with 
some query optimization.

I've included a bunch of diagnostic info below.  If there's anything
else I should be providing, please let me know!

tblIbmast  ~ 220,000 rows
tblUsage   ~ 130,000 rows
tblSlmmast ~   2,000 rows

The explain information and the query follow shortly.  Note that this 
_is_ the query I want -- it's not like I'm doing some kind of weird
cartesian product kind of thing without wanting to.  The results 
returned tabulate correctly as checked against an independent 
source.

Right now, on a Pentium 2 @ 450MHz w/320mb (RH Linux 5.2 w/Kernel 2.0.36), 
this query is taking ~ 0.75 seconds.  On a Pentium @ 150 MHz w/128mb, the 
query takes ~ 4.5 seconds.  These figures strike me as being unintuitively 
long -- there isn't that much logic going on inside the "select" statement, 
and the "explain" seems to be telling me that it'll only be looping over 2030 
rows.  Surely this kind of computational horsepower could chew through 2030 
rows in faster than 0.75 (or 4.5) seconds.

These tables are freshly created, and don't need either an 'optimize' or an
isamchk command to fix them up.

So, either...
  a) my intuition is wrong
  b) I'm not noticing something about the query which suggests it should
     take longer than what I think it would, or
  c) the query could be re-written more efficiently 

I'm willing to believe any of these, but I'm hoping it's (c), since that's
fixable.  And the best way to prove that it's (c) is to actually have a 
re-written query I could try running, right? :-)

Thanks very much for looking this stuff over.  I appreciate the time spent.

Cheers,
            Richard

+-------+--------+---------------+---------+---------+----------+------+----
---+
| table | type   | possible_keys | key     | key_len | ref      | rows |
Extra |
+-------+--------+---------------+---------+---------+----------+------+----
---+
| i     | ref    | PRIMARY,sku   | sku     |       4 | 20143100 |  203 |
   |
| u     | ref    | sku           | sku     |       4 | i.sku    |   10 |
   |
| slm   | eq_ref | PRIMARY       | PRIMARY |      12 | i.locno  |    1 |
   |
+-------+--------+---------------+---------+---------+----------+------+----
---+
3 rows in set (0.02 sec)

mysql> select slm.svar, u.qtyused, i.goh, i.opt
    -> from   tblIbmast i
    ->        left join tblUsage     u on u.partused = i.sku
    ->                                      and u.sl = i.locno
    ->        left join tblSlmmast slm on slm.sloc   = i.locno
    -> where      i.sku = '20143100'
    ->        and i.locno <> 'SHADOW'
    ->        and i.locno <> '99033';
+-------+---------+------+------+
| svar  | qtyused | goh  | opt  |
+-------+---------+------+------+
| 50344 | NULL    |    0 |    0 |
| 50921 | NULL    |    1 |    1 |
| 50924 | 1       |    0 |    1 |
| 50924 | 1       |    0 |    1 |
| 50924 | 1       |    0 |    1 |
.
.
.
| 50944 | 1       |    0 |    0 |
| 50947 | 1       |    0 |    0 |
| 50947 | 1       |    0 |    0 |
| 50933 | NULL    |    1 |    0 |
| 50943 | 1       |    0 |    0 |
| 50961 | 1       |    0 |    0 |
+-------+---------+------+------+
299 rows in set (0.74 sec)

mysql> describe tblIbmast;
+------------+--------------------------+------+-----+----------+-------+
| Field      | Type                     | Null | Key | Default  | Extra |
+------------+--------------------------+------+-----+----------+-------+
| line       | int(11)                  |      |     | 0        |       |
| sku        | int(8) unsigned zerofill |      | PRI | 00000000 |       |
| locno      | varchar(12)              |      | PRI |          |       |
| noakey     | varchar(12)              | YES  |     | NULL     |       |
| goh        | int(11)                  | YES  |     | NULL     |       |
| boh        | int(11)                  | YES  |     | NULL     |       |
| goo        | int(11)                  | YES  |     | NULL     |       |
| boo        | int(11)                  | YES  |     | NULL     |       |
| git        | int(11)                  | YES  |     | NULL     |       |
| bit        | int(11)                  | YES  |     | NULL     |       |
| gbo        | int(11)                  | YES  |     | NULL     |       |
| bbo        | int(11)                  | YES  |     | NULL     |       |
| gal        | int(11)                  | YES  |     | NULL     |       |
| bal        | int(11)                  | YES  |     | NULL     |       |
| goodytd    | int(11)                  | YES  |     | NULL     |       |
| badytd     | int(11)                  | YES  |     | NULL     |       |
| goodpyr    | int(11)                  | YES  |     | NULL     |       |
| badpyr     | int(11)                  | YES  |     | NULL     |       |
| iuytd      | int(11)                  | YES  |     | NULL     |       |
| iupyr      | int(11)                  | YES  |     | NULL     |       |
| opt        | int(11)                  | YES  |     | NULL     |       |
| max        | int(11)                  | YES  |     | NULL     |       |
| manordate  | varchar(5)               | YES  |     | NULL     |       |
| reord      | int(11)                  | YES  |     | NULL     |       |
| bin1       | varchar(6)               | YES  |     | NULL     |       |
| bin2       | varchar(6)               | YES  |     | NULL     |       |
| cyclicdate | varchar(5)               | YES  |     | NULL     |       |
| lineupdate | varchar(5)               | YES  |     | NULL     |       |
| rolldate   | varchar(5)               | YES  |     | NULL     |       |
| lastupd    | varchar(16)              | YES  |     | NULL     |       |
+------------+--------------------------+------+-----+----------+-------+
30 rows in set (0.00 sec)

mysql> describe tblUsage;
+------------+--------------------------+------+-----+------------+-------+
| Field      | Type                     | Null | Key | Default    | Extra |
+------------+--------------------------+------+-----+------------+-------+
| line       | int(11)                  |      |     | 0          |       |
| closedate  | date                     |      |     | 0000-00-00 |       |
| sl         | varchar(8)               | YES  |     | NULL       |       |
| callno     | varchar(7)               |      |     |            |       |
| oem        | varchar(4)               | YES  |     | NULL       |       |
| svar       | varchar(5)               | YES  |     | NULL       |       |
| partused   | int(8) unsigned zerofill |      | MUL | 00000000   |       |
| qtyused    | varchar(4)               |      |     |            |       |
| classtype  | char(1)                  | YES  |     | NULL       |       |
| partrecov  | varchar(8)               | YES  |     | NULL       |       |
| aveused    | float(10,2)              |      |     | 0.00       |       |
| averecov   | float(10,2)              |      |     | 0.00       |       |
| qtyrecov   | varchar(4)               | YES  |     | NULL       |       |
| repaircost | float(10,2)              |      |     | 0.00       |       |
| difference | float(10,2)              |      |     | 0.00       |       |
+------------+--------------------------+------+-----+------------+-------+
15 rows in set (0.00 sec)

mysql> describe tblSlmmast;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| line  | int(11)                  |      | UNI | 0       |       |
| sloc  | varchar(12)              |      | PRI |         |       |
| name  | varchar(40)              | YES  |     | NULL    |       |
| svar  | int(5) unsigned zerofill |      | MUL | 00000   |       |
+-------+--------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> \s
--------------
mysql  Ver 9.27 Distrib 3.22.15-gamma, for pc-linux-gnu (i686)

Connection id:          31
Current database:       RawData
Current user:           root@localhost
Server version          3.22.15-gamma
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /tmp/mysql.sock
Uptime:                 3 hours 57 min 11 sec

Threads: 10  Questions: 530066  Slow queries: 0  Opens: 22  Flush tables: 1
 Open tables: 10
--------------

========================================================================

$ mysqldump -d RawData tblIbmast tblUsage tblSlmmast

# MySQL dump 5.12
#
# Host: localhost    Database: RawData
#--------------------------------------------------------
# Server version        3.22.15-gamma

#
# Table structure for table 'tblIbmast'
#
CREATE TABLE tblIbmast (
  line int(11) DEFAULT '0' NOT NULL,
  sku int(8) unsigned zerofill DEFAULT '00000000' NOT NULL,
  locno varchar(12) DEFAULT '' NOT NULL,
  noakey varchar(12),
  goh int(11),
  boh int(11),
  goo int(11),
  boo int(11),
  git int(11),
  bit int(11),
  gbo int(11),
  bbo int(11),
  gal int(11),
  bal int(11),
  goodytd int(11),
  badytd int(11),
  goodpyr int(11),
  badpyr int(11),
  iuytd int(11),
  iupyr int(11),
  opt int(11),
  max int(11),
  manordate varchar(5),
  reord int(11),
  bin1 varchar(6),
  bin2 varchar(6),
  cyclicdate varchar(5),
  lineupdate varchar(5),
  rolldate varchar(5),
  lastupd varchar(16),
  PRIMARY KEY (sku,locno),
  KEY sku (sku),
  KEY locno (locno)
);

#
# Table structure for table 'tblUsage'
#
CREATE TABLE tblUsage (
  line int(11) DEFAULT '0' NOT NULL,
  closedate date DEFAULT '0000-00-00' NOT NULL,
  sl varchar(8),
  callno varchar(7) DEFAULT '' NOT NULL,
  oem varchar(4),
  svar varchar(5),
  partused int(8) unsigned zerofill DEFAULT '00000000' NOT NULL,
  qtyused varchar(4) DEFAULT '' NOT NULL,
  classtype char(1),
  partrecov varchar(8),
  aveused float(10,2) DEFAULT '0.00' NOT NULL,
  averecov float(10,2) DEFAULT '0.00' NOT NULL,
  qtyrecov varchar(4),
  repaircost float(10,2) DEFAULT '0.00' NOT NULL,
  difference float(10,2) DEFAULT '0.00' NOT NULL,
  KEY sku (partused)
);

#
# Table structure for table 'tblSlmmast'
#
CREATE TABLE tblSlmmast (
  line int(11) DEFAULT '0' NOT NULL,
  sloc varchar(12) DEFAULT '' NOT NULL,
  name varchar(40),
  svar int(5) unsigned zerofill DEFAULT '00000' NOT NULL,
  PRIMARY KEY (sloc),
  UNIQUE line (line),
  KEY svar (svar)
);

----------------------------------------------------------------
 Shads -- help rebuild the community * http://shadnet.shad.ca/
 Richard Dice * ShadNet Creator * richard.dice@stripped
 Voice/Cell/Voicemail 416 992 9568 * Fax 416 992 9571

Thread
Humbly looking for help in optimizing a queryRichard Dice11 Mar
  • Humbly looking for help in optimizing a queryMichael Widenius11 Mar
Re: new MySQL clientMichael Widenius20 Apr