Performance problem with mysql

[daymon]

I have a server with Debian 3.0 Woody installed. And i use it as webserver and MySQL server.

When i do an SELECT query with two LEFT JOIN and an LIMIT to 20 rows. It takes 6 seconds to complete the query on an PIII 700 MHz and 256 MB of RAM. I have about 6000 rows in the database.

Exactly the same web site and database commpletes the same query in 1.3 second on my frinds server that runs Windows on an AMD Athlon 900 MHz. The only diference is that he fetched 1000 rows from the database.

Thankfull for help... I have no clue why it takes so long time.

[ultraslacker]

Very little to say except for check your indexes.

Post the query and the explain - EXPLAIN SELECT * ...

[daymon]

mysql> EXPLAIN SELECT * FROM guestbook;
+-----------+------+---------------+------+---------+------+------+-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------+------+---------------+------+---------+------+------+-------+
| guestbook | ALL | NULL | NULL | NULL | NULL | 7956 | |
+-----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

This is how it looks...

But why does it go so much faster on Windows than on Linux. It´s exactly the same database. The database i made at the windows workstation and then dumped to a textfile and imported to the linux server.

[Julian_Pedley]

Hi

I wonder if the MSQL documentation "How MySQL Optimises LIMIT" is relevant.

Perhaps the query is iniitally returning all the rows in both cases inwhich case the speeds are comparable?

[ultraslacker]

No, I didn't mean do an EXPLAIN SELECT on any old query, but on the query in question. Your slow query had a 2 left joins. Why don't you do some troubleshooting - compare the explains on both boxes and see if the slower one is due to indexes.

I wonder if the MSQL documentation "How MySQL Optimises LIMIT" is relevant.

Absolutely - I think this problem can be solved by some RTFM.

[jasper]

As said before, try find out whether the same indexes exist on the tables involved.

Does the Linux-box have a /etc/my.cnf with some tweaked MySQLd parameters? The defaults are quite conservative, especially sort, key and join buffers can make a difference. See http://www.mysql.com/doc/en/Server_parameters.html. From the MySQL docs: "When you have installed MySQL, the support-files' directory will contain some differentmy.cnf' example files, my-huge.cnf',my-large.cnf', my-medium.cnf', andmy-small.cnf', you can use as a base to optimise your system."

[daymon]

I have solved the slow-query problem now.

Instead of the query taking 6.5 sec it´s now taking only 0,63 sec.

I created som index on the unique row that are involved with the LEFT JOIN query.

Now the current performance will do for some time.

Thanks for all the help.