MySQL Forums
Forum List  »  Custom Storage Engines

Multiple full table scan during filesort
Posted by: Ladislav Sopko
Date: January 08, 2016 01:40PM


I'm creating custom storage engine for integration with our FullText retrieval DB.

Suppose that I have this table:

CREATE TABLE `test`.`bar` (
`rowid` int,
`ndoc` int,
`id` varchar(10) NOT NULL default '.',
`random` VARCHAR(50) NOT NULL,
`dictionary` VARCHAR(256),
`text` longtext,
`xml` longtext,
`extra` text,
PRIMARY KEY (`rowid`),
INDEX `ix_ndoc` (`ndoc`),
INDEX `ix_id` (`id`),
INDEX `ix_random` (`random`)

When I execute this query:

select rowid, ndoc, id, random, dictionary, `text`
where (rowid is not null or extra in ('[xml,/bar/dictionary]=PRECO*'))
order by id ;

Consider that Where condition is always true cause its wanted that MySql dont check it,
I use pushed down condition to filter data.

My Problem is:

My Sql do correctly 2 times table scan, cause first time it should load columns necessary for file sort, then it should do file sort
and then it should do second table scan using sorted data, It work as is, BUT

It ask for all columns in both scans, AND that's the problem calculation for text column is very expensive for me
and it is not necessary for sort, I thought that in read_set it should flag just wanted columns, but it apparently does not function
cause it ask in bot runs all used columns.

What I'm doing wrong?

Thank you


Options: ReplyQuote

Written By
Multiple full table scan during filesort
January 08, 2016 01:40PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.