Subject: I have problem with optimizor

Mailing List

Home

Forum Home

MySQL General - General MySQL discussion

MySQL++ - Programming with the C++ API to MySQL

MaxDB - Everything about MaxDB, formerly known as SAP DB

ODBC - ODBC with the MySQL Connector/ODBC driver

MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP

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

•	Subject: mysql openssl Question
•	ERROR 1045: Access denied for user: 'root@localhost ' (Using password: NO)
•	Update one field with more fields from another table
•	Subject: Getting Identity after INSERT
•	ERROR 2002: Can 't connect to local MySQL server through socket
•	mysql test 4 1 fails with the gis test
•	Subject: MySQL Cluster Software
•	Downgrade Mysql from 4 to 3 23
•	Mysql 4 0 Oracle Stored Procedure Trigger Conversion
•	Can 't access mysql after kernel upgrade
•	Executing MySQL Commands From Within C Program
•	Comparing and writing out BLOBS
•	Subject: Re: Preventing Duplicate Entries
•	FULLTEXT query format question
•	Strange behavior, Table Level Permission
•	Does the binary log enabling affect the MySQL performances?
•	mysql:it 's a db not a dbms how it 's possible?!
•	mysql have same function mthod as Oracle decode()

Subject: I have problem with optimizor

Subject: I have problem with optimizor

2007-08-16 - By ??????? ???????

Back
I'm using Maxdb 7.6.0.16 and I tested this inquiry on 7.6.0.37 and I
have same result.

REFS.t_smu - view

first inquery work longer then second inquery

this first inquery:
SELECT i.counter
FROM GIS.invest i,
REFS.t_smu s,
TEMP.DOITDoctorAccess a
WHERE i.dateres = '2007-08-14' AND value(i.factresid, i.resid) = s.smuid
AND (s.resid = a.resid AND (s.edate is null or s.edate>=dateres)
AND s.bdate<dateres)

EXPLAIN

A
TABLE SCAN 6

C INVESTID JOIN VIA KEY COLUMN 1

TABLE HASHED

B INVESTID JOIN VIA KEY COLUMN 1

TABLE HASHED

D UNIQUE_REVISION JOIN VIA MULTIPLE INDEXED COLUMNS 80

INVESTID (USED INDEX COLUMN)

REVISION (USED INDEX COLUMN)

E UNIQUE_REVISION JOIN VIA MULTIPLE INDEXED COLUMNS 80

INVESTID (USED INDEX COLUMN)

REVISION (USED INDEX COLUMN)

NO TEMPORARY RESULTS CREATED
INTERNAL TEMPORARY RESULT
TABLE SCAN 1

A
JOIN VIA KEY RANGE 0

TABLE TEMPORARY SORTED

RESID (USED SORT COLUMN)

I INVEST_DATERES JOIN VIA INDEXED COLUMN 111035

DATERES (USED INDEX COLUMN)

RESULT IS COPIED , COSTVALUE IS 21

this second inquery:
SELECT i.counter
FROM (SELECT * FROM GIS.invest WHERE dateres = '2007-08-14') i,
REFS.t_smu s,
TEMP.DOITDoctorAccess a
WHERE value(i.factresid, i.resid) = s.smuid
AND (s.resid = a.resid AND (s.edate is null or s.edate>=dateres)
AND s.bdate<dateres)

EXPLAIN

GIS INVEST INVEST_DATERES EQUAL CONDITION FOR INDEX 16740

DATERES (USED INDEX COLUMN)

A
TABLE SCAN 6

C INVESTID JOIN VIA KEY COLUMN 1

TABLE HASHED

B INVESTID JOIN VIA KEY COLUMN 1

TABLE HASHED

D UNIQUE_REVISION JOIN VIA MULTIPLE INDEXED COLUMNS 80

INVESTID (USED INDEX COLUMN)

REVISION (USED INDEX COLUMN)

E UNIQUE_REVISION JOIN VIA MULTIPLE INDEXED COLUMNS 80

INVESTID (USED INDEX COLUMN)

REVISION (USED INDEX COLUMN)

NO TEMPORARY RESULTS CREATED
GIS INVEST INVEST_DATERES EQUAL CONDITION FOR INDEX 16740

DATERES (USED INDEX COLUMN)
INTERNAL TEMPORARY RESULT
TABLE SCAN 1

A
JOIN VIA KEY RANGE 0

TABLE TEMPORARY SORTED

RESID (USED SORT COLUMN)
INTERNAL TEMPORARY RESULT
JOIN VIA KEY RANGE 1

TABLE TEMPORARY SORTED

??????????????????????????????????????????????????????????????????????????????
?????????????????? (USED SORT COLUMN)

RESID (USED SORT COLUMN)

RESULT IS COPIED , COSTVALUE IS 3

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