MySQL Forums
Forum List  »  MyISAM

Old Performance Issues, now resolved? Or wait until MySQL 6?
Posted by: Steven Roussey
Date: November 10, 2005 07:44PM

From an old email converation long, long, ago...

From: Sasha Pachev
> From: Steven Roussey
> > 2) select bigblob where optimized_index_clause order by
> > optimized_index_clause limit 6650,50
> >
> > Problem: I used to think that since the start number is high in the limit,
> > that it was slow because the data was not often used and therefore not
> > cached (the second try at the query is fast). I think that is part right --
> > I think the problem is that MySQL starts using the data file (with the big
> > blob) before it needs to. Ideally (and maybe MySQL does this) it should use
> > the index file only until the where _and_ limit clauses are satisfied, then
> > go get data from the data file. My gut tells be that MySQL is causing lots
> > of the blob data to be read from disk even though it is not needed. I have
> > the EXPLAIN below.
>
> I think you are right. The first workaround that comes to my head is to
> select all the matching values of the primary key into temp table, and then
> just perform a join. Monty may have some additional comments.

Has the status of this changed? I am doing some re-optimizing of our SQL and came across our use of temp tables on the application side to get around this problem, and wanted to know if it can be cleaned up, and what version would be needed. I think Monty suggested some post 4.0 time table (this was back in the 3.23.x days). Any change?

Also, in MyISAM (though I don't think in InnoDB), there was an issue that MySQL gets the entire record and puts it into memory before looking at what columns were needed. If you had big blobs, then this would cause real issues (especially if you were in the case above). If each record had 512KB, and you selected 1000 records, but judiciously removed that column, then you would still get an extra 5MB for that query. I thought this was on a TODO list as well back in 2001. Or maybe I wasn't very persuasive. :)

Options: ReplyQuote


Subject
Views
Written By
Posted
Old Performance Issues, now resolved? Or wait until MySQL 6?
2610
November 10, 2005 07:44PM


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.