Re: query slow

[luiz Rafael]

Hello friends

Id like to thanks all friends that helped with this question

Regards
Luiz

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query slow

[Jay Pipes]

luiz Rafael wrote:

Dear friends

is their any way to optimize this query bellow, it take +- 2minutes do 
complete, i think it becouse their no index by the emissao field


SELECT  * FROM  `sav00_sava0400_dbf`   WHERE 2000 = YEAR(`emissao`) OR 
(1999 = YEAR(`emissao`) AND 12  MONTH(`emissao`))  ORDER BY emissao ASC


SELECT  * FROM  `sav00_sava0400_dbf`
emissao BETWEEN '2000-01-01' AND '2000-12-31'
UNION ALL
SELECT  * FROM  `sav00_sava0400_dbf`
emissao BETWEEN '1999-12-01' AND '1999-12-31'

--
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Roaming North America, based in Columbus, Ohio
email: [EMAIL PROTECTED]mob: +1 614 406 1267

Are You MySQL Certified? http://www.mysql.com/certification
Got Cluster? http://www.mysql.com/cluster

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query slow

[luiz Rafael]

Hi Jay

Thanks for the help

Regards
Luiz

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query slow

[Eugene Kosov]

Jay Pipes wrote:

SELECT  * FROM  `sav00_sava0400_dbf`
emissao BETWEEN '2000-01-01' AND '2000-12-31'
UNION ALL
SELECT  * FROM  `sav00_sava0400_dbf`
emissao BETWEEN '1999-12-01' AND '1999-12-31'


Why not:

SELECT  * FROM  `sav00_sava0400_dbf`
emissao BETWEEN '1999-12-01' AND '2000-12-31'

?? ;)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query slow

[Eugene Kosov]

luiz Rafael wrote:
SELECT  * FROM  `sav00_sava0400_dbf`   WHERE 2000 = YEAR(`emissao`) OR 
(1999 = YEAR(`emissao`) AND 12  MONTH(`emissao`))  ORDER BY emissao ASC
Are you sure this is what you really want? MONTH() is never greater than 
12, so your query is equal to:
   SELECT  * FROM  `sav00_sava0400_dbf`   WHERE 2000 = YEAR(`emissao`) 
ORDER BY emissao


In order to make your query work faster you should create an index on 
`emissao` and rewrite it using date ranges.
So you query will become something like this (if you actually meant 
December'99 and whole year 2000):
 SELECT * FROM `sav00_sava0400_dbf` WHERE `emissao` BETWEEN '1999-12-01 
00:00:00' AND '2000-12-31 23:59:59';


--
BR,
Eugene Kosov

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Query slow (again)

[Gelu Gogancea]

Hi,
Try this...
Do an alter and create indexes on the following fields of your tables:
-wmkt_email_sent.fkemail
-wmkt_client.fkemail
-wmkt_maillist_client.fkclient
... and i think it's a good idea if you create indexes on the primary keys
of each table.It's redundant but have some effect.

Regards,
Gelu
_
G.NET SOFTWARE COMPANY

Permanent e-mail address : [EMAIL PROTECTED]
  [EMAIL PROTECTED]
- Original Message -
From: Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED]
To: MYSQL [EMAIL PROTECTED]
Sent: Tuesday, May 14, 2002 8:31 PM
Subject: Query slow (again)


 Hi,

 I have changed my query but it is so slow :( Please, help me!

 Thank u very much!
 Edilson.

 -
 Query -
 SELECT a.idemail, a.fklastresp
 FROM wmkt_email a, wmkt_client b, wmkt_maillist_client c
 LEFT JOIN wmkt_email_sent d ON a.idemail=d.fkemail
 WHERE d.fkemail IS NULL
 AND c.bActive AND c.fkmaillist IN (2)
 AND a.idemail=b.fkemail AND c.fkclient=b.idclient
 ORDER BY fklastresp LIMIT 1000

 -
 Tables structures -
 
 Table,Create Table
 wmkt_email,CREATE TABLE `wmkt_email` (
`idemail` int(11) NOT NULL auto_increment,
`email` varchar(255) NOT NULL default '',
`fklastresp` int(11) NOT NULL default '0',
PRIMARY KEY  (`idemail`),
UNIQUE KEY `ixEmail` (`email`)
 ) TYPE=MyISAM
 
 
 Table,Create Table
 wmkt_client,CREATE TABLE `wmkt_client` (
`idclient` int(11) NOT NULL auto_increment,
`realname` varchar(80) NOT NULL default '',
`fkemail` int(11) NOT NULL default '0',
`dtInsert` datetime default NULL,
`dtLastUp` datetime default NULL,
`fkuser` int(11) NOT NULL default '1',
PRIMARY KEY  (`idclient`),
KEY `ixFkemail` (`fkemail`)
 ) TYPE=MyISAM
 
 -
 Table,Create Table
 wmkt_maillist_client,CREATE TABLE `wmkt_maillist_client` (
`fkmaillist` int(11) NOT NULL default '0',
`fkclient` int(11) NOT NULL default '0',
`dtInsert` datetime default NULL,
`bActive` tinyint(4) NOT NULL default '1',
KEY `ixEmailList` (`fkmaillist`,`fkclient`)
 ) TYPE=MyISAM
 
 -
 Table,Create Table
 wmkt_email_sent,CREATE TABLE `wmkt_email_sent` (
   `idemailsent` int(11) NOT NULL auto_increment,
   `fkpbl` int(11) NOT NULL default '0',
   `fkemail` int(11) NOT NULL default '0',
   `dtSend` datetime default NULL,
   `nResult` int(11) NOT NULL default '0',
   `dtLastUp` datetime default NULL,
   `nMachine` int(11) NOT NULL default '0',
   PRIMARY KEY  (`idemailsent`),
   UNIQUE KEY `ixUEmailPbl` (`fkemail`,`fkpbl`),
   KEY `ixnMacPbl` (`nMachine`,`fkpbl`,`fkemail`)
 ) TYPE=MyISAM
 

 -
 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




-
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