Indexes in 4.0.13

I have two machines, one runs RedHat 9.0 and MySQL 4.0.13 and the other
runs RedHat 7.3 and MySQL 3.23.49. The hardware is identical (they are
primary and backup servers for an intranet site and we are currently
upgrading the software - one at a time).

I have noticed that scripts that execute quickly on the 3.23 machine
take a long time on the 4.0 machine and it appears to be tied to indexes.

I have the same table on both machines and it has three INT(11) values
in each row, none are primary keys but they are all indexed.

Here is the description of the table from desc.....

+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| PeopleId | int(11) | YES | MUL | NULL | |
| MenuId | int(11) | YES | MUL | NULL | |
| SequenceId | int(11) | YES | MUL | NULL | |
+------------+---------+------+-----+---------+-------+

When I create and index on a table containing 176,000 rows it takes 6.0
seconds on the 4.0 server but for the same index on the 3.23 server it
takes 2.2 seconds.

Both servers have MyISAM tables (and just to be sure I dumped the table
from the 3.23 server and loaded it onto the 4.0 machine to make sure
that data was the same). I created many different indexes on both
machinie and the effect was evident in all cases.

Cheers,

Paul






--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Indexes in 4.0.13

Paul Coldrey writes:
> I have two machines, one runs RedHat 9.0 and MySQL 4.0.13 and the other
> runs RedHat 7.3 and MySQL 3.23.49. The hardware is identical (they are
> primary and backup servers for an intranet site and we are currently
> upgrading the software - one at a time).
>
> I have noticed that scripts that execute quickly on the 3.23 machine
> take a long time on the 4.0 machine and it appears to be tied to indexes.
>
> I have the same table on both machines and it has three INT(11) values
> in each row, none are primary keys but they are all indexed.
>
> Here is the description of the table from desc.....
>
> +------------+---------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+---------+------+-----+---------+-------+
> | PeopleId | int(11) | YES | MUL | NULL | |
> | MenuId | int(11) | YES | MUL | NULL | |
> | SequenceId | int(11) | YES | MUL | NULL | |
> +------------+---------+------+-----+---------+-------+
>
> When I create and index on a table containing 176,000 rows it takes 6.0
> seconds on the 4.0 server but for the same index on the 3.23 server it
> takes 2.2 seconds.
>
> Both servers have MyISAM tables (and just to be sure I dumped the table
> from the 3.23 server and loaded it onto the 4.0 machine to make sure
> that data was the same). I created many different indexes on both
> machinie and the effect was evident in all cases.
>
> Cheers,
>
> Paul

Hi!

This list is dedicated to the repeatable test cases, which means that
we require entire table, queries and my.cnf's to repeat the problem.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB
/_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator
<___/ www.mysql.com Larnaca, Cyprus


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Indexes in 4.0.13

Hi!

Thank you.
I was able to repeat it.
You can follow the progress of your bugreport at
http://bugs.mysql.com/712

Regards,
Sergei

On Jun 23, Paul Coldrey wrote:
> I have two machines, one runs RedHat 9.0 and MySQL 4.0.13 and the other
> runs RedHat 7.3 and MySQL 3.23.49. The hardware is identical (they are
> primary and backup servers for an intranet site and we are currently
> upgrading the software - one at a time).
>
> I have noticed that scripts that execute quickly on the 3.23 machine
> take a long time on the 4.0 machine and it appears to be tied to indexes.
>
> I have the same table on both machines and it has three INT(11) values
> in each row, none are primary keys but they are all indexed.
>
> Here is the description of the table from desc.....
>
> +------------+---------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+---------+------+-----+---------+-------+
> | PeopleId | int(11) | YES | MUL | NULL | |
> | MenuId | int(11) | YES | MUL | NULL | |
> | SequenceId | int(11) | YES | MUL | NULL | |
> +------------+---------+------+-----+---------+-------+
>
> When I create and index on a table containing 176,000 rows it takes 6.0
> seconds on the 4.0 server but for the same index on the 3.23 server it
> takes 2.2 seconds.
>
> Both servers have MyISAM tables (and just to be sure I dumped the table
> from the 3.23 server and loaded it onto the 4.0 machine to make sure
> that data was the same). I created many different indexes on both
> machinie and the effect was evident in all cases.
>
> Cheers,
>
> Paul
--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org