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: Shawn Hood <shawnlhood AT gmail.com>
  • To: "Internetworkers: http://www.ibiblio.org/internetworkers/" <internetworkers AT lists.ibiblio.org>
  • Subject: Re: [internetworkers] Suggested place for mysql performance
  • Date: Tue, 9 Mar 2010 00:39:51 -0500

See EXPLAIN and DESCRIBE. Execute. Post output.


On Mar 9, 2010, at 12:23 AM, Jeremy Portzer wrote:

> 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
> ---
> Come and play at the InterNetWorkers Web site!
> http://www.ibiblio.org/internetworkers/
> You are currently subscribed to InterNetWorkers mailing list
> To unsubscribe visit
> http://lists.ibiblio.org/mailman/listinfo/internetworkers





Archive powered by MHonArc 2.6.24.

Top of Page