Skip to Content.
Sympa Menu

internetworkers - [internetworkers] Suggested place for mysql performance

internetworkers AT lists.ibiblio.org

Subject: Internetworkers: http://www.ibiblio.org/internetworkers/

List archive

Chronological Thread  
  • From: Scott Russell <catfather AT donpoo.net>
  • To: "Internetworkers: http://www.ibiblio.org/internetworkers/" <internetworkers AT lists.ibiblio.org>
  • Subject: [internetworkers] Suggested place for mysql performance
  • Date: Mon, 8 Mar 2010 23:20:41 -0500

Greets all.

I'm looking for a good online MySQL resource that can help me with a
performance problem I've run into. Is anyone a member of a good community or
can suggest one? I've tried the forums on mysql.com without a positive
response.

Without going into too much detail, I'm looking to understand why a simple
left join (with index of course) isn't performing as expected on a large set
of tables. The following takes about 130s to complete. I'm using a "small"
test database derived from production data where the 'cm' table 29922 rows
and a cm_disk table is 4638480 rows. The result set is 654472.

EXPLAIN SELECT `iteration`, `time`, `raidname`, `diskbusy` FROM (`cm`) JOIN
`cm_disk` using (cmid) WHERE `perfid` = '4' AND `filerid` = '5';
+----+-------------+---------+------+------------------------+----------------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+---------+------+------------------------+----------------+---------+---------------+------+-------+
| 1 | SIMPLE | cm | ref | PRIMARY,perfid_filerid | perfid_filerid
| 8 | const,const | 3275 | |
| 1 | SIMPLE | cm_disk | ref | cmid | cmid
| 8 | devel.cm.cmid | 161 | |
+----+-------------+---------+------+------------------------+----------------+---------+---------------+------+-------+
2 rows in set (0.00 sec)


-- Scott





Archive powered by MHonArc 2.6.24.

Top of Page