List:General Discussion« Previous MessageNext Message »
From:Steven Roussey Date:January 26 2003 8:34pm
Subject:Re: How to speed things up in MySQL ?
View as plain text  
> I guess the reason for this is that the I have a some blob fields
whitch are
> all used.. (each record consist of approx 600 KB...)

There it is. MySQL's MyISAM will get the whole record even if it only
needs a part. You can try InnoDB as it does it differently. 

[At some point I may try and add that capability to MyISAM. I've been
researching good ways to compress text and still search it in its
compressed state. Then I hope to add the ability of MyISAM tables to
automatically compress TEXT/VARCHAR/CHAR (non binary) fields without
worrying about uncompressing everything if someone does a LIKE on that
field. BLOBs can use gzip type compression. And neither the data nor
decompression should occur if the field is not used. When I get more
time...]

However, back to your issue. Have you tried adding
INDEX(ProductionYear,ID)? That would prevent MySQL from having to read
the datafile at all, since it could find everything it needs in the
index file.

My speed guesses:

o If you didn't change the structure of the table or add an index, then
switching to InnoDB would improve performance quite a bit.

o Adding an index(ProductionYear) to the above InnoDB table would speed
things even more.

o Adding INDEX(ProductionYear,ID) to the MyISAM table would be even
faster still.

-steve-

http://Network54.com


Thread
How to speed things up in MySQL ?Thomas Kvamme25 Jan
  • Re: How to speed things up in MySQL ?Bhavin Vyas25 Jan
  • Re: How to speed things up in MySQL ?Benjamin Pflugmann25 Jan
    • RE: How to speed things up in MySQL ?Loren McDonald26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
    • RE: How to speed things up in MySQL ?Markus Gieppner26 Jan
    • Re: How to speed things up in MySQL ?Benjamin Pflugmann26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
  • Re: How to speed things up in MySQL ?Frederick L. Steinkopf26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
    • Re: How to speed things up in MySQL ?Sergei Golubchik26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
    • Re: How to speed things up in MySQL ?Benjamin Pflugmann26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme26 Jan
Re: How to speed things up in MySQL ?Steven Roussey26 Jan
  • Re: How to speed things up in MySQL ?Thomas Kvamme27 Jan