From: Reindl Harald
Date: January 21 2011 7:56pm
Subject: Re: Slow query on MySQL4 server doing simple inner join of two InnoDB
 tables
List-Archive: http://lists.mysql.com/mysql/224187
Message-Id: <4D39E4E4.8090801@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigFD6F20C193A1B61D403C9D5C"

--------------enigFD6F20C193A1B61D403C9D5C
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

you need hughe ram / innodb_buffer_pool for large datasets
in a perfect world the buffer_pool is as large as the data

how looks your current config?
how much RAM has the machine?

Am 21.01.2011 20:21, schrieb Kendall Gifford:
> Hello everyone, I've got a database on an old Fedora Core 4 server runn=
ing
> MySQL 4 (mysql-server.x86_64 4.1.12-2.FC4.1). The database in question =
has
> just two (InnoDB) tables:
>=20
> messages (approx 2.5 million records)
> recipients (approx 6.5 million records)
>=20
> These track information about email messages. Each message "has many"
> recipient records. The structure of the two tables (omitting irrelevant=
 data
> fields) are as follows:
>=20
> +-------------+------------------+------+-----+---------------------+--=
--------------+
> | Field       | Type             | Null | Key | Default             |
> Extra          |
> +-------------+------------------+------+-----+---------------------+--=
--------------+
> | id          | int(10) unsigned |      | PRI | NULL                |
> auto_increment |
> | sent_at     | datetime         |      | MUL | 0000-00-00 00:00:00
> |                |
> | ......................... OTHER FIELDS OMITTED FOR BREVITY
> ....................... |
> +-------------+------------------+------+-----+---------------------+--=
--------------+
>=20
> +-------------+------------------+------+-----+---------------------+--=
--------------+
> | Field       | Type             | Null | Key | Default             |
> Extra          |
> +-------------+------------------+------+-----+---------------------+--=
--------------+
> | id          | int(10) unsigned |      | PRI | NULL                |
> auto_increment |
> | message_id  | int(10) unsigned |      | MUL | 0
> |                |
> | employee_id | int(10) unsigned | YES  | MUL | NULL
> |                |
> | ......................... OTHER FIELDS OMITTED FOR BREVITY
> ....................... |
> +-------------+------------------+------+-----+---------------------+--=
--------------+
>=20
> I have the following query that is just too slow:
>=20
>> SELECT messages.* FROM messages
>> INNER JOIN recipients ON recipients.message_id =3D messages.id
>> WHERE recipients.employee_id =3D X
>> GROUP BY messages.id
>> ORDER BY sent_at DESC
>> LIMIT 0, 25;
>=20
> This takes about 44 seconds on average. The query explanation is as fol=
lows:
>=20
> +----+-------------+------------+--------+--------------------------+--=
------------+---------+-------------------------------------+--------+---=
-------------------------------------------+
> | id | select_type | table      | type   | possible_keys            |
> key          | key_len | ref                                 | rows   |=

> Extra                                        |
> +----+-------------+------------+--------+--------------------------+--=
------------+---------+-------------------------------------+--------+---=
-------------------------------------------+
> |  1 | SIMPLE      | recipients | ref    | messages_fk,employee_idx |
> employee_idx |       5 | const                               | 222640 |=

> Using where; Using temporary; Using filesort |
> |  1 | SIMPLE      | messages   | eq_ref | PRIMARY                  |
> PRIMARY      |       4 | email_archive.recipients.message_id |      1
> |                                              |
> +----+-------------+------------+--------+--------------------------+--=
------------+---------+-------------------------------------+--------+---=
-------------------------------------------+
>=20
> I've been doing some searching on the web and have no idea if/how this =
can
> be sped up. Most searches these days reference MySQL 5.x which I'm just=
 not
> sure how much applies. I'm hoping that there is something obvious that =
I'm
> missing, or that one of you experts knows what I might be able to chang=
e to
> speed this query up.
>=20
> Anyhow, thanks in advance for even so much as reading my message, let a=
lone
> replying :).
>=20

--=20

Mit besten Gr=FC=DFen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofm=FChlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/


--------------enigFD6F20C193A1B61D403C9D5C
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk055OQACgkQhmBjz394AnlOwwCdFxuqaCnGb5qaH2BUl/NPSXua
COgAn1IFXn0x77szBgaJcyJBjb5DlZ5L
=P979
-----END PGP SIGNATURE-----

--------------enigFD6F20C193A1B61D403C9D5C--