Mailing List
Home
MySQL General - General MySQL discussion
MaxDB - Everything about MaxDB, formerly known as SAP DB
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
MySQL++ - Programming with the C++ API to MySQL
ODBC - ODBC with the MySQL Connector/ODBC driver
Java Help - Mostly related to the MySQL Connector/J driver
Perl - Perl support for MySQL with DBI and DBD::mysql
GUI - MySQL GUI Tools
Announcement
Subjects
• mysql openssl Question
• mysql:it 's a db not a dbms how it 's possible?!
• Does the binary log enabling affect the MySQL performances?
• Strange behavior, Table Level Permission
• FULLTEXT query format question
• Preventing Duplicate Entries
• Comparing and writing out BLOBS
• Executing MySQL Commands From Within C Program
• Can 't access mysql after kernel upgrade
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Downgrade Mysql from 4 to 3 23
• MySQL Cluster Software
• mysql test 4 1 fails with the gis test
• ERROR 2002: Can 't connect to local MySQL server through socket
• Getting Identity after INSERT
• Update one field with more fields from another table
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• mysql have same function mthod as Oracle decode()
 
Search:  
Power your search with and, or, +, -, or "some phrase" operators.
strange optimizer / index use ??

strange optimizer / index use ??

2004-04-06       - By Gerd König

 Back
Reply:     1     2     3     4  

--Boundary-00=_h8scAeJZMyvMhGN
Content-Type: text/plain;
 charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

we're wondering about the heavy load our maxdb produces.
After some time of investigation we found out that the database produces
a lot of table scans.
Why we are wondering is, that our currently running 7.3.0.46 doesn't produce
table scans, furthermore this version uses the created indices (and MaxDB
doesn't...).

An "explain" of a test statement shows us a completely different behaviour of
statement execution. The costvalue from MaxDB is 10-times the value from
the 7.3.0.46 :-((

This behaviour is hardware-independant (we tested different cases), and we
wanted to know if there is a possibility to force MaxDB using indices for
reducing load.

The explain outputs of our test-statement for both cases are attached.

Perhaps problems with multiple-column indices ???

We want to upgrade our currently running database version, so we need help,
because this heavy load isn't that funny...

thanks a lot
-GERD-

--Boundary-00=_h8scAeJZMyvMhGN
Content-Type: text/plain;
 charset="us-ascii";
 name="explain_162.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
  filename="explain_162.txt"

  SD     TABLE SCAN        1112  
  D   DELIVERY_ID   JOIN VIA KEY COLUMN        1223  
  S   SLOT_ID   JOIN VIA KEY COLUMN        7180  
  T   TIME_ID   JOIN VIA KEY COLUMN           8  
  G   GATE_ID   JOIN VIA KEY COLUMN           5  
          RESULT IS COPIED   , COSTVALUE IS       17471  

--Boundary-00=_h8scAeJZMyvMhGN
Content-Type: text/plain;
 charset="us-ascii";
 name="explain_163.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
  filename="explain_163.txt"

  S   DELIVERY_DAY   EQUAL CONDITION FOR INDEXED COLUMN        4506          
  T   TIME_ID   JOIN VIA KEY COLUMN           8          
  SD   TAB_TISLOT_SLOT_DELIVERY_SK3   JOIN VIA RANGE OF MULTIPLE INDEXED COL.      
1053          
    SLOT_ID       (USED INDEX COLUMN)            
  D   DELIVERY_ID   JOIN VIA KEY COLUMN        1195          
  G   GATE_ID   JOIN VIA KEY COLUMN           2          
TISYS           RESULT IS COPIED   , COSTVALUE IS        1726          


--Boundary-00=_h8scAeJZMyvMhGN
Content-Type: text/plain; charset=us-ascii

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=mysql@(protected)
--Boundary-00=_h8scAeJZMyvMhGN--