[SOLVED] Upgrading MySQL from 4.0.21 to 5.0.27

[Linvo]

Hello,

I upgraded MySQL from 4.0.21. to 5.0.27 (It was about time...!)

The main issue now is that it's awfully slow  :(  :-\ comparing to 4.0.21

Cannot figure out why. Tried a lot of changing parameters, configuration issues, but nothing seems to speed it up.

Somebody any ideas?

Thanks!

[fenway]

What's slow?

[Linvo]

Well, slow is a query that takes 1 sec with 4.0.21, takes now 30 seconds with 5.0.27

Same query, same conditions, only difference is the MySQL version.

[fenway]

Post the query, and the EXPLAIN output.

[Linvo]

To POST the query and the EXPLAIN output is rather difficult.
Because, the query is actually a routine that consists of multiple query's formulated in PHP classes.
So, speaking about "a query" was rather misleading.

I'll try to explain.

I use PHP classes to build MySQL query's.
For instance a SELECT query.

Also I use a PHP class for getting meta information from a MySQL (MyISAM) table.
For instance, what are the fieldnames, what is the PK in the table, which signature does the table has, what are the fieldtypes, what's the last record, etc. That information is used to build the query's.
So, when I want to select records from a table, I create an instance of the DBtable class for the specific MySQL table in PHP and furthermore I create an instance of the SELECT class in PHP and give the instance of the table class as an (input) argument to it.
The instance of the SELECT class does have methods to create select query's to get values from fields, or get records form the specific table that meet certain conditions, etc.

The reason why I do this like this, is because I can use these PHP classes within al my PHP applications, without writing for each application the specific MySQL query's. It's a sort of standard interface to my MySQL databases.

One thing though is not possible (yet), and that's to get the output from an EXPLAIN.


Maybe there's another way to find out why the difference in speed is so dramatic?
In both versions the way the query's are build and how the tables are defined including their definition and indexes are the same.

[fenway]

I understand that you've abstracted the DB... but somewhere at the "end" it runs an actual query, just echo that, store it to a file, return it, whatever.  And just because the query and the tables are the same, doesn't mean the optimizer/parser treats them the same way in different versons.

[Linvo]

Fenway,

I've finally a clue about the problem. It had, i'm relieved, nothing to do with the upgrading. For some not yet understandable reason, a virusscanner which is active on my LAN became active during consulting the MySQL server from any computersystem in my LAN. This was not the case with version 4.0.27 but for some reason it came alive with version 5.0.27. Having disabled this scanner, the speed of both versions are the same.

Thanks anyway for your attention.

[fenway]

No problem... probably wasn't on the list of exclusions anymore, different name/path.