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()
 
Joins, reporting bug in search strategy.

Joins, reporting bug in search strategy.

2007-08-06       - By Lukasz Misztal

 Back
Hi,

this is prabably bug in search strategy plan.
Is it enough to report bug?

And second question,
is any simple way to force 'table scan' on query (avoid indexes)?

Best regards,

Lukasz

-- ---- ---- ---- ---(Base query)-- ---- ---- ----
select * from "DokumentLokalu"
left join "TowarFizycznie" on
"DokumentLokalu"."DKL_ID"="TowarFizycznie"."TFZ_IDDokumentLokalu"
left join "MagazynTowar" on
"TowarFizycznie"."TFZ_IDTowar"="MagazynTowar".MT_ID
left join "Kwota" on   "TowarFizycznie".TFZ_ID="Kwota"."KW_IDTowarFizycznie"
left join "DokumentLokaluTowarDJ" on
"TowarFizycznie"."TFZ_ID"="DokumentLokaluTowarDJ"."DTJ_IDDokumentLokaluTowar"

execution plan:
MAXIPROD   DokumentLokalu     TABLE SCAN           1  
MAXIPROD   TowarFizycznie   TFZ_IX_IDDokumentlokalu   JOIN VIA INDEXED COLUMN
        1  
    TFZ_IDDokumentLokalu       (USED INDEX COLUMN)    
MAXIPROD   MagazynTowar   MT_ID   JOIN VIA KEY COLUMN           5  
MAXIPROD   Kwota   KW_IX_IDTowarFizycznie   JOIN VIA INDEXED COLUMN           1  
    KW_IDTowarFizycznie       (USED INDEX COLUMN)    
MAXIPROD   DokumentLokaluTowarDJ     JOIN VIA KEY RANGE           1  
      TABLE TEMPORARY SORTED    
    DTJ_IDDokumentLokaluTowar       (USED SORT COLUMN)    
          RESULT IS COPIED   , COSTVALUE IS          11  

-- ---- ---- ---- ---(condition, wrong result)-- ---- ---- ----
Base query+
where
"MagazynTowar"."MT_IDJednostka"="DokumentLokaluTowarDJ"."DTJ_IDJednostka"
-- ---- ---- ---- ---- ------

search strategy:
MAXIPROD   MagazynTowar     TABLE SCAN           5  
MAXIPROD   DokumentLokalu     TABLE SCAN           1  
MAXIPROD   DokumentLokaluTowarDJ     JOIN VIA KEY RANGE           1  
      TABLE TEMPORARY SORTED    
    DTJ_IDJednostka       (USED SORT COLUMN)    
MAXIPROD   TowarFizycznie   TFZ_ID   JOIN VIA KEY COLUMN           1  
MAXIPROD   Kwota   KW_IX_IDTowarFizycznie   JOIN VIA INDEXED COLUMN           1  
    KW_IDTowarFizycznie       (USED INDEX COLUMN)    
          RESULT IS COPIED   , COSTVALUE IS         923  


-- ---- ---- ---- --(second wrong result)-- ---- ---- ---- ----
Base query+
where
"MagazynTowar"."MT_IDJednostka"+1="DokumentLokaluTowarDJ"."DTJ_IDJednostka"+1
-- ---- ---- ---- ---- ------

search strategy:
MAXIPROD   MagazynTowar     TABLE SCAN           5  
MAXIPROD   DokumentLokalu     TABLE SCAN           1  
MAXIPROD   DokumentLokaluTowarDJ   JOIN VIA KEY RANGE   1  
      TABLE TEMPORARY SORTED    
    ?????????????????????????????????????????????????????????????????????????????
(USED SORT COLUMN)    
    DTJ_IDJednostka       (USED SORT COLUMN)    
MAXIPROD   TowarFizycznie   TFZ_ID   JOIN VIA KEY COLUMN   1  
MAXIPROD   Kwota   KW_IX_IDTowarFizycznie   JOIN VIA INDEXED COLUMN           1  
    KW_IDTowarFizycznie       (USED INDEX COLUMN)    
  RESULT IS COPIED   , COSTVALUE IS         923  


-- ---- ---- ---- ---- ---- --(Correct result)-- ---- ---- ---- ----
Base query+
where
"MagazynTowar"."MT_IDJednostka"-"DokumentLokaluTowarDJ"."DTJ_IDJednostka"=0
-- ---- ---- ---- ---- ------

search strategy:
MAXIPROD   DokumentLokalu     TABLE SCAN           1  
MAXIPROD   TowarFizycznie   TFZ_IX_IDDokumentlokalu   JOIN VIA INDEXED COLUMN
        1  
    TFZ_IDDokumentLokalu       (USED INDEX COLUMN)    
MAXIPROD   MagazynTowar   MT_ID   JOIN VIA KEY COLUMN           5  
MAXIPROD   Kwota   KW_IX_IDTowarFizycznie   JOIN VIA INDEXED COLUMN           1  
    KW_IDTowarFizycznie       (USED INDEX COLUMN)    
MAXIPROD   DokumentLokaluTowarDJ     JOIN VIA KEY RANGE           1  
      TABLE TEMPORARY SORTED    
    DTJ_IDDokumentLokaluTowar       (USED SORT COLUMN)    
          RESULT IS COPIED   , COSTVALUE IS          11  




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