Skip to Content.
Sympa Menu

internetworkers - Re: [internetworkers] Suggested place for mysql performance

internetworkers AT lists.ibiblio.org

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

List archive

Chronological Thread  
  • From: Jeremy Portzer <jeremyp AT pobox.com>
  • To: "Internetworkers: http://www.ibiblio.org/internetworkers/" <internetworkers AT lists.ibiblio.org>
  • Subject: Re: [internetworkers] Suggested place for mysql performance
  • Date: Tue, 09 Mar 2010 16:23:26 +1100

Scott Russell wrote:
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)


I agree with Cristobal, a describe of the two tables would be helpful. Also do you have an explicitly-defined foreign key constraint between the two? What's the primary key of the 'cm' table?

I'm more of an Oracle adherent so I'm not familiar with the "USING" syntax for joins, which seems to be a MySQL extension. Personally I would have written the join above as follows (removing needless quotes and parens), which is the portable, ANSI SQL method:

SELECT iteration, time, raidname, diskbusy
FROM cm
LEFT JOIN cm_disk
ON cm.id = cmdisk.cmid
WHERE perfid = '4' AND filerid = '5';

Notice I'm assuming your primary key in the 'cm' table is called 'id', but I have no evidence to support that.

Doing a quick Google search on the 'USING' syntax ( http://www.davidkmuir.com/blogs/mysqls-join-using-syntax ) - it seems this requires the columns to be named the same in both tables, e.g. your primary key would have to be cm.cmid rather than cm.id. Could a naming issue there be part of the problem?

In Oracle it would be a good idea to build a primary key constraint as that would help the optimizer 'know' that joins on those column pairs will occur. If that makes sense for your data, and you don't already have it in place, I'd recommend doing so, but I'm not sure if that actually helps performance in MySQL or not.

Also, in your message you describe your statement as a "simple left join" - but it's actually an inner join, not a left [outer] join. To review, an outer join (left or right) means that you want to include all rows in the (left or right) side of the join, even if they don't exist in the other side. According to the above reference, MySQL's USING syntax generates an inner join, meaning that the results must exist in both tables to be included in the result set. An inner join is the "natural" way of doing joins, unless you specify something else.

Hope this helps,
Jeremy




Archive powered by MHonArc 2.6.24.

Top of Page