SitePoint Sponsor

User Tag List

Results 1 to 11 of 11

Thread: query problem

  1. #1
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    query problem

    hi
    please can someone advice me why this query doesnt work...
    Code:
    SELECT m.MoveTime, m.MoveY, m.MoveX, m.MoveSpeed, m.MoveAngle, ms.MotorValue,
               c.CarName, c.CarDescription, c.CarVoicePhone,
               d.Drivername, d.DriverDesc
               FROM move as m, motor as ms, cars as c
               LEFT JOIN (button as b, drivers as d)
               ON (m.carID = b.CarID AND d.Button = b.Button)
               WHERE m.CarID = '1000001'
               AND c.CarID = m.CarID
               AND m.CarID = ms.CarID
               AND m.MoveTime = ms.MotorTime
    the fields are ok...something with left join i think...
    and the b.CarID may be null though...
    any suggestion appreciated...
    thanx in advance

  2. #2
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code MySQL:
    SELECT m.MoveTime, 
           m.MoveY, 
           m.MoveX, 
           m.MoveSpeed, 
           m.MoveAngle, 
           ms.MotorValue,
           c.CarName, 
           c.CarDescription, 
           c.CarVoicePhone,
           d.Drivername, 
           d.DriverDesc
      FROM move as m
    INNER
      JOIN motor as ms
        ON ms.CarID = m.CarID
    INNER
      JOIN cars as c
        ON c.CarID = m.CarID
    LEFT OUTER
      JOIN button as b
        ON b.CarID = m.carID
    LEFT OUTER
      JOIN drivers as d
        ON d.Button = b.Button
     WHERE m.CarID = '1000001'
    Could you paste your database schema? It seems like m.MoveTime = ms.MotorTime condition is redundant.

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanx a lot...
    it works perfectly!

  4. #4
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by earl-grey View Post
    Code MySQL:
    SELECT m.MoveTime, 
           m.MoveY, 
           m.MoveX, 
           m.MoveSpeed, 
           m.MoveAngle, 
           ms.MotorValue,
           c.CarName, 
           c.CarDescription, 
           c.CarVoicePhone,
           d.Drivername, 
           d.DriverDesc
      FROM move as m
    INNER
      JOIN motor as ms
        ON ms.CarID = m.CarID
    INNER
      JOIN cars as c
        ON c.CarID = m.CarID
    LEFT OUTER
      JOIN button as b
        ON b.CarID = m.carID
    LEFT OUTER
      JOIN drivers as d
        ON d.Button = b.Button
     WHERE m.CarID = '1000001'
    Could you paste your database schema? It seems like m.MoveTime = ms.MotorTime condition is redundant.
    this query works fine localy but on the server it produces a "Got error 28 from storage engine"?
    that error means that this query uses a lot of space on the server to perform this task!
    anyone know how to obtain the same query but with less disk space usage?
    any suggestion appreciated..
    thanx in advance

  5. #5
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you have any indexes created on the tables?

  6. #6
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes
    Code:
    tables                  indexes
    ---------------------------------------------
    cars                    carid, cargroupid
    button                  carid, buttontime, button
    drivers                 driverid, button
    motor                   carid, motortime, motorvalue
    move                    carid, movetime
    but tables are in MyISAM format not in InnoDB...
    version 4.1.22-standard

  7. #7
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you paste the EXPLAIN output for the query?

  8. #8
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT m.MoveTime, m.MoveY, m.MoveX, m.MoveSpeed, m.MoveAngle,
               ms.MotorValue,
               c.CarName, c.CarDescription, c.CarVoicePhone,
               d.DriverName, d.DriverDesc
               FROM move as m
               INNER JOIN motor as ms ON ms.CarId = m.CarId
               INNER JOIN cars as c ON c.CarID = m.CarID
               LEFT OUTER JOIN button as b ON b.CarID = m.carID
               LEFT OUTER JOIN drivers as d ON d.Button = b.Button
               WHERE m.CarID = '1000001'
               ORDER BY m.MoveTime DESC LIMIT 1
    
    table, type,     possible_keys,  key,          key_len, ref,          rows,      Extra
    'm',    'ref',      'CarID,More',     'CarID',      4,         'const',     151019,   'Using where; Using filesort'
    'c',     'eq_ref', 'PRIMARY',        'PRIMARY',  4,        'm.CarID',   1, ''
    'b',     'ref',      'CarID,More',     'CarID',      4,         'm.CarID',  67, ''
    'd',     'ref',      'Button',           'Button',    8,         'b.Button',  1, ''     
    'ms',   'ref',      'CarID,More',     'CarID',      4,         'm.CarID',   1221, ''

  9. #9
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try creating a composite index on two columns (CarID and MoveTime) of the move table .

  10. #10
    SitePoint Wizard
    Join Date
    Mar 2004
    Posts
    1,647
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "More" is the composite index of CarID and MoveTime...

  11. #11
    An average geek earl-grey's Avatar
    Join Date
    Mar 2005
    Location
    Ukraine
    Posts
    1,403
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Add USE INDEX (More) after FROM move as m.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •