Hi,
I have installed mysql max 4.0.4 beta on a Suse (ver. 8.0) linux server and
I have created a database with this schema:
CREATE TABLE ecoras (
rsnum tinyint(4) NOT NULL,
rsori tinyint(1) NOT NULL,
rscod int(11) NOT NULL,
rsid int(11) NOT NULL auto_increment,
rsdat date NOT NULL default '0000-00-00',
rstda date NOT NULL default '0000-00-00',
rstcd int(11) NOT NULL default '0',
rstec int(11) default NULL,
rstes varchar(50) NOT NULL default '',
rstpg varchar(6) NOT NULL default '0',
rsart varchar(250) NOT NULL default '',
rsimm varchar(12) NOT NULL default '',
rsimr varchar(12) default NULL,
rsrub int(11) default NULL,
rsrub2 int(11) default NULL,
rsord int(11) default NULL,
rspst int(11) default NULL,
rsgrd char(3) default NULL,
rsric smallint(6) default NULL,
rsdis smallint(6) default NULL,
rssel smallint(6) default NULL,
rscom1 varchar(100) default NULL,
rscom2 varchar(250) default NULL,
rscom3 varchar(250) default NULL,
rssot varchar(100) default NULL,
rsoch varchar(100) default NULL,
rsgio1 int(11) default NULL,
rsgio2 int(11) default NULL,
rsint1 int(11) default NULL,
rsint2 int(11) default NULL,
rsarg1 int(11) default NULL,
rsarg2 int(11) default NULL,
rsarg3 int(11) default NULL,
rsnom1 int(11) default NULL,
rsnom2 int(11) default NULL,
rsnom3 int(11) default NULL,
rstip char(1) default NULL,
rsind tinyint(1) default NULL,
datsys date NOT NULL default '0000-00-00',
timsys time NOT NULL default '00:00:00',
rsdel enum('Y','N') default 'N',
PRIMARY KEY (rsnum,rsori,rscod),
UNIQUE KEY rsid (rsid),
KEY rsdat (rsdat,rstda,rstes,rstpg),
KEY rstda (rstda,rstcd,rstpg,rsart),
KEY rstcd (rstcd,rstda,rstpg),
KEY rstcd2 (rstcd,rsdat,rstpg),
KEY rsimm (rsimm),
KEY rsgio1 (rsgio1),
KEY rsgio2 (rsgio2),
KEY rsint1 (rsint1),
KEY rsint2 (rsint2),
KEY rsarg1 (rsarg1),
KEY rsarg2 (rsarg2),
KEY rsarg3 (rsarg3),
KEY rsnom1 (rsnom1),
KEY rsnom2 (rsnom2),
KEY rsnom3 (rsnom3),
KEY rsind (rsind,datsys,timsys)
) TYPE=MyISAM;
# --------------------------------------------------------
CREATE TABLE ecorub (
rsnum tinyint(4) NOT NULL,
rsrub int(11) NOT NULL auto_increment,
rsdes varchar(50) NOT NULL default '',
rssig varchar(15) NOT NULL default '',
rsrif int(11) default NULL,
rsrif2 int(11) default NULL,
rsrif3 int(11) default NULL,
rsrif4 int(11) default NULL,
rsrif5 int(11) default NULL,
rsord int(11) default NULL,
rsrms char(1) default NULL,
rstmp int(11) default NULL,
PRIMARY KEY (rsnum,rsrub),
KEY rsord (rsnum,rsord,rsdes,rsrub),
KEY rsdes (rsnum,rsdes,rsrub),
KEY rssig (rsnum,rssig,rsrub),
KEY rsrif (rsnum,rsrif,rsrub),
KEY rsrif2 (rsnum,rsrif2,rsrub),
KEY rsrif3 (rsnum,rsrif3,rsrub),
KEY rsrif4 (rsnum,rsrif4,rsrub),
KEY rsrif5 (rsnum,rsrif5,rsrub),
KEY rsrms (rsnum,rsrms,rsrub)
) TYPE=MyISAM;
# --------------------------------------------------------
CREATE TABLE ecotes (
rstcd int(11) NOT NULL auto_increment,
rstes varchar(100) NOT NULL default '',
rstec int(11) default NULL,
rstmp int(11) default NULL,
rsord int(11) default NULL,
rsdir varchar(100) default NULL,
rscon int(11) default NULL,
rsweb enum('Y','N') default 'Y',
rsdin datetime default NULL,
rsdup datetime default NULL,
PRIMARY KEY (rstcd),
KEY ecotes_2 (rstes,rstcd),
KEY ecotes_3 (rstec,rstcd),
KEY ecotes_4 (rsord,rstes,rstcd),
KEY rsdir (rsdir,rstcd),
KEY rscon (rscon,rstcd),
KEY rsdin (rsdin,rstcd),
KEY rsdup (rsdup,rstcd),
KEY rstmp (rstmp,rstcd)
) TYPE=MyISAM;
# --------------------------------------------------------
CREATE TABLE ecotxt (
rsori tinyint(1) NOT NULL default '0',
rscod int(11) NOT NULL default '0',
rstxt mediumtext,
PRIMARY KEY (rsori, rscod),
FULLTEXT KEY rstxt (rstxt)
) TYPE=MyISAM;
# --------------------------------------------------------
And I try to execute this query:
SELECT ecoras.rstda, ecotes.rstes, ecoras.rsart, ecoras.rsimm,
ecorub.rsdes, ecoras.rstpg, ecoras.rsdat, ecoras.rsrub
FROM ecoras, ecotxt
LEFT JOIN ecotes ON ecoras.rstcd = ecotes.rstcd
LEFT JOIN ecorub ON ecoras.rsnum = ecorub.rsnum and ecoras.rsrub =
ecorub.rsrub
WHERE ecoras.rsori = ecotxt.rsori
AND ecoras.rscod = ecotxt.rscod
AND (rsdat >= '2002-12-01') and (rsdat <= '2002-12-23')
AND (match(ecotxt.rstxt) against('JAVA' IN BOOLEAN MODE))
ORDER BY ecoras.rspst, ecoras.rstda, ecotes.rstes, ecoras.rstpg,
ecoras.rsart
I have no data in my resultset, but data-base contains data which match
conditions.
If I excute the following query I have a valid resultset.
SELECT ecoras.rstda, ecotes.rstes, ecoras.rsart, ecoras.rsimm,
ecorub.rsdes, ecoras.rstpg, ecoras.rsdat, ecoras.rsrub
FROM ecoras, ecotxt
LEFT JOIN ecotes ON ecoras.rstcd = ecotes.rstcd
LEFT JOIN ecorub ON ecoras.rsnum = ecorub.rsnum and ecoras.rsrub =
ecorub.rsrub
WHERE ecoras.rsnum = 0
AND ecoras.rsori = ecotxt.rsori
AND ecoras.rscod = ecotxt.rscod
AND (rsdat >= '2002-12-01') and (rsdat <= '2002-12-23')
AND (match(ecotxt.rstxt) against('JAVA' IN BOOLEAN MODE))
ORDER BY ecoras.rspst, ecoras.rstda, ecotes.rstes, ecoras.rstpg,
ecoras.rsart
In this query the where-clause matches the primary key (index) of ecoras
table:
ecoras.rsnum = 0
AND ecoras.rsori = ecotxt.rsori
AND ecoras.rscod = ecotxt.rscod
If I do not use an index, I will obtain slow performances, but I must have
always a valid resultset like in the second query.
Is it a bug or a join-condition has to match an index?
Thanks and best regard
Walter Procopio
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
