Need help optimizing a batch query

Similar Threads:

1.Need Help Optimizing A Query

I'm trying to optimize the following query, which is getting called 
many times per second:

Essentially if there is a Message in the table where MsgSubType = 1 or
2,
I want to select the first message in the table (with the lowest
Message Id)

If there isn't a message with MsgSubType = 1 or 2, then I want to
select
the first message in the table that has a MsgSubType = 3, 4 or 5,
again
the one with the lowest message id.   

So essentially I'm using the DB as a prioritized FIFO queue.
I'm wondering if TOP is the way to go here?  And the ORDER BY, is
there
a better way to do this.   The query only needs to return 1 row.

I'm currently have a clustered index on Message.MsgSubType, a
non-clustered index on Message.Type and a non-clustered index on
Message.MessageId

Thanks very much for any help.

Here's the query:

if exists( select * from Message  where (Message.MsgSubType = 1 or   
       Message.MsgSubType = 2)) 
BEGIN
    	SELECT TOP 1 MessageID, Type, Message, DATALENGTH(Message)  
            as "MessageLength", IdNum, MsgSubType
   	  FROM Message
     	 WHERE  Message.MsgSubType = 1 
           or   Message.MsgSubType = 2
     	ORDER BY MessageID
END
ELSE  /* These messages have lowest priority */
BEGIN
     SELECT TOP 1 MessageID,Type, Message, DATALENGTH(Message)   
         as "MessageLength", IdNum, MsgSubType
     FROM Message
     WHERE Message.Message.MsgSubType = 3
         or        Message.MsgSubType = 4
         or        Message.Type = 5
     ORDER BY MessageID  
    
END

2.Need help optimizing this query

Hi everyone,
I've been trying to get this query to run quicker.  Currently it just takes 
too long.  Does anyone have any suggestions about optimizing it?

SELECT DISTINCT outerquery.name, "group" FROM analog outerquery WHERE EXISTS 
(SELECT * FROM analog innerquery WHERE innerquery."group" = 
outerquery."group" AND innerquery.ptnum != outerquery.ptnum AND 
innerquery.name LIKE 'ZZZ_%') AND outerquery.name LIKE 'ZZZ_%'

3.I need help optimizing this query

Here is the query I am currently using.
It seems to take forever. It keeps wanting to use filesort and a temporary can 
anyone plz help me. I will send the creates for both tables.

select collections.`id`,collections.`subject`,collections.`numfiles`,collections.
`groups`,
collections.`size`,collections.`from`,collections.`date`,collections.`nfo`
FROM temp.collections,temp.nfo where match (nfo.nfo) against ('$searchterm' 
in boolean mode) and nfo.cid = collections.id order by `date` desc limit 0,50

CREATE TABLE `nfo` (
`id` mediumint(6) unsigned NOT NULL auto_increment,
`cid` int(10) unsigned NOT NULL,
`fid` int(10) unsigned NOT NULL,
`nfo` mediumtext NOT NULL,
`filename` varchar(200) NOT NULL,
PRIMARY KEY (`id`),
KEY `cid` (`cid`),
KEY `fid` (`fid`),
FULLTEXT KEY `nfo` (`nfo`)
) ENGINE=MyISAM AUTO_INCREMENT=152507 DEFAULT 
CHARSET=latin1

CREATE TABLE `collections` (
`id` int(10) unsigned NOT NULL auto_increment,
`subject` text NOT NULL,
`filename` text NOT NULL,
`numfiles` smallint(4) unsigned NOT NULL default '0',
`groups` text NOT NULL,
`partids` mediumtext NOT NULL,
`size` bigint(20) unsigned NOT NULL default '0',
`from` tinytext NOT NULL,
`date` int(10) unsigned NOT NULL default '0',
`group1` smallint(4) unsigned NOT NULL,
`group2` smallint(4) unsigned default NULL,
`group3` smallint(4) unsigned default NULL,
`group4` smallint(4) unsigned default NULL,
`group5` smallint(4) unsigned default NULL,
`nfo` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `filename_U` (`filename`(255)),
KEY `from` (`from`(50)),
KEY `date` (`date`),
KEY `Size` (`size`),
KEY `nfo` (`nfo`),
KEY `groupindex` USING BTREE (`group1`,`group2`,`group3`,`group4`,
`group5`),
FULLTEXT KEY `Subject_full` (`subject`)
) ENGINE=MyISAM AUTO_INCREMENT=1865103 DEFAULT 
CHARSET=latin1 MIN_ROWS=100000 MAX_ROWS=2500000 
AVG_ROW_LENGTH=500

4.JOINed ORDERed LIMITed query not optimized enough - need help

Hi all,

I need to retrieve 13 rows from a 100,000-row JOIN, starting with a row
with given values.
All required indexes are available AFAICT, yet EXPLAIN tells me mysql is
"Using index; Using temporary; Using filesort" on the first table it's
touching.

I don't know where I'm going wrong, or whether I'm hitting a limitation
of mysql's query optimizer. Any help appreciated.

DETAILS

mysql version is 5.0.15.

Tables are set up like this:

-- Main categories. An ID and a name.
CREATE TABLE main (
   id int(9),
   name varchar(40),
   PRIMARY KEY (id),
   KEY name (name)
) TYPE = InnoDB;
-- Sample categories. Choose random numbers so that id order and name
-- order are different.
INSERT INTO main VALUES
   (0, CONCAT(RAND(), ' main 0')),
   ...
   (9, CONCAT(RAND(), ' main 9'));
-- Just not to leave this stone unturned:
ANALYZE TABLE main;

-- Subcategories. ID-plus-name.
-- An additional foreign key that links up with the 'main' table.
CREATE TABLE sub (
   id int(9),
   name varchar(40),
   main_id int(9),
   PRIMARY KEY (id),
   KEY name_main (name, main_id),
   KEY main_name (main_id, name)
) TYPE = InnoDB;
INSERT INTO sub VALUES
   (000, CONCAT(RAND(), ' main 0 sub 000'), 0),
   ...
   (999, CONCAT(RAND(), ' main 0 sub 099'), 9);
ANALYZE TABLE sub;

-- Finally, detail records, all 100,000 of them.
CREATE TABLE detail (
   id int(9),
   name varchar(40),
   sub_id int(9),
   PRIMARY KEY (id),
   KEY name_sub (name, sub_id),
   KEY sub_name (sub_id, name)
) TYPE = InnoDB;
INSERT INTO detail VALUES
   (00000, CONCAT(RAND(), ' sub 000 detail 00000'), 000),
   ...
   (99999, CONCAT(RAND(), ' sub 999 detail 99999'), 999);
ANALYZE TABLE detail;


Now I want to present a detail-sub-main list to the end user:

SELECT * FROM
   main
   JOIN sub ON main.id = sub.main_id
   JOIN detail ON sub.id = detail.sub_id

I want this to be sorted by detail name, then sub name, finally main name:

SELECT * FROM
   main
   JOIN sub ON main.id = sub.main_id
   JOIN detail ON sub.id = detail.sub_id
   ORDER by detail.name, sub.name, main.name

The user isn't supposed to see the entire list, he's scrolling through 
it. Let's say he's at the record with detail name '0.5xxxx' and needs to 
see the next 12 records, then I have

SELECT * FROM
   main
   JOIN sub ON main.id = sub.main_id
   JOIN detail ON sub.id = detail.sub_id
   WHERE detail.name > '0.5'
   ORDER by detail.name, sub.name, main.name
   LIMIT 12

This takes just 0.46-0.54 seconds to complete on my machine, but the 
real thing with 150,000 detail records and four additional JOINs to 
produce auxiliary information is unbearably slow: 25-40 seconds.

Now EXPLAIN tells me mysql started with the 'main' table - that's just 
backwards, it should have started with 'detail' to find just the first 
twelve rows, then work up through 'sub' to 'main'!
It's no surprise that it then needed to "use temporary; use filesort".

Well, to speed up this particular case, I might be able to use a 
STRAIGHT JOIN. However, that would require reordering the JOIN part of 
the query, and I'd like to avoid that if possible - I'd have to do some 
serious rewriting of the dynamic SQL part of the application.

Hints, tips, improvements very much appreciated.

Regards,
Jo

5.Need help to optimize query

Hi all,



I need help to optimize this query:



select s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, 
s.potrazuje, s.saldo from saldakonti s

where

s.datum<'2005-01-01' and

s.uplata='R' and

exists (select t.temeljnica, t.rbr from saldakonti t where

                t.konto=s.konto and

                t.sifra_partnera=s.sifra_partnera and

                t.vezni_broj=s.vezni_broj and

                t.datum<'2005-01-01' and

                (t.uplata='S' or t.uplata='U')

                group by t.temeljnica, t.rbr

                having

                abs(sum(t.duguje-t.potrazuje))<>abs(s.saldo))

order by

s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, s.potrazuje, 
s.saldo



 Currently this query takes forever to execute. Any suggestions appreciated.

Thanks in advance.



Josko

6. Need help optimizing a stored procedure - SQL2k

7. Need Help Optimizing a SPROC

8. Need (more) help optimizing for SQL BackEnd