The older system is choosing to use a different index. I would suggest
running an analyze on your new tables and see if you can get the newer
system to use the same Postsindex8 index.
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 2/4/04, 1:01:46 PM, Alan <[EMAIL PROTECTED]> wrote regarding index change
moving files to other computer?:
> Hi folks. I'm in the midst of moving from a debian unstable system to a
> gentoo system and I'm having some problems getting mysql to give me the
> same performance. On the new gentoo system (with more hardware) a fairly
> complex query (a search on a UBBThreads forum) is taking 10+ seconds to
> complete, while on the debian system it's in the 0.01s range. Before
> I'm critisized on my distro choice, it appears I've traced part of the
> problem down with EXPLAIN:
> (sorry about the width :( )
> New but slower system:
>
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+------+----------------------
-------+
> | table | type | possible_keys
| key | key_len | ref | rows | Extra
|
>
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+------+----------------------
-------+
> | t1 | ref | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx
| w3t_Postsindex7 | 3 | const | 6607 | Using where; Using
filesort |
> | t2 | eq_ref | indx1
| indx1 | 100 | t1.B_Board | 1 |
|
> | t3 | eq_ref | PRIMARY,indx3
| PRIMARY | 4 | t1.B_PosterId | 1 |
|
>
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+------+----------------------
-------+
> Old but faster system:
>
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+-------+-------------+
> | table | type | possible_keys
| key | key_len | ref | rows | Extra |
>
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+-------+-------------+
> | t1 | range | w3t_Postsindex7,w3t_Postsindex8,ID_ndx,board_topic_ndx
| w3t_Postsindex8 | 4 | NULL | 19645 | Using where |
> | t2 | eq_ref | indx1
| indx1 | 100 | t1.B_Board | 1 | |
> | t3 | ref | PRIMARY,indx3
| indx3 | 4 | t1.B_PosterId | 1 | |
>
+-------+--------+--------------------------------------------------------
+-----------------+---------+---------------+-------+-------------+
> The big thing here as I've read and understood it is that the gentoo
> system is "Using filesort", which is horribly slow compared to plain old
> where and indexes.
> However, I've made no changes to the database files, just copied
> /var/lib/mysql/<database> from the old system to the new. The faster
> box is a 4.0.14 system and the slower is using 4.0.16.
> Based on my reading of some of the docs on mysql.com using filesort is
> used when mysql can't use indexes for the order by clause. Would these
> indexes not be there and still available when moved to the new system?
> It seems very strange to me. Oh, and I also tried dumping just that
> database and re-importing it from the (sql) dump file, with the same
> results.
> Please help!
> BTW, specs on the systems:
> Old: debian unstable running linux 2.4.24 with mysql 4.0.14
> XP1800 with 1G ram on two IDE drives with software RAID1
> New: gentoo stable, kernels used were 2.4.24, 2.4.25_pre6, and 2.6.1
> with and without preempt. Mysql tried 4.0.16 static and dynamic
> with various cflags and 4.0.17 binaries from mysql.com. my.cnf has
> been set to the same as o nthe old box, the default config, and the
> huge, large and medium sample configs, all with the same results.
> Many thanks.
> alan
> --
> Alan <[EMAIL PROTECTED]> - http://arcterex.net
> --------------------------------------------------------------------
> "There are only 3 real sports: bull-fighting, car racing and mountain
> climbing. All the others are mere games." -- Hemingway
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
