SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Jul 2006
    Posts
    22
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Optimization problem (JOINs and ORDER)

    Hello,

    I am optimizing my queries for my web app and I ran into one problem. How do I optimize this query:

    Code MySQL:
    SELECT 
    students.id, 
    students.firstname, 
    rosters.nickname, 
    students.lastname, 
    rosters.clas , 
    students_info.value AS label , 
    LEFT(CONCAT(IF( rosters.nickname="", students.firstname, CONCAT(rosters.nickname, " (", students.firstname, ")") ), " ", students.lastname), 26) AS name, 
    attendance.field,
    attendance.reason, 
    attendance_codes.name AS attendance
     
    FROM rosters, students 
     
    LEFT JOIN students_info ON 
    students_info.teacher='1' AND students_info.label='8' AND students_info.clas='2' AND students_info.student=students.id 
     
    LEFT JOIN attendance ON 
    attendance.student=students.id AND attendance.clas='2' AND attendance.when='2009-01-26' 
     
    LEFT JOIN attendance_codes ON 
    attendance_codes.teacher='1' AND attendance_codes.id=attendance.field 
     
    WHERE rosters.student=students.id AND rosters.clas='2' AND rosters.dropped=FALSE 
     
    ORDER BY name DESC

    I think I got the correct indexes (which ones would you use?). Query takes about 0.0080 sec on 10000 rows. Can I improve this speed?

    Thank you in advance,
    Dmitriy
    Last edited by dmitriy; Jan 26, 2009 at 19:16.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    hmmm.... 0.0080 sec on 10000 rows...

    what kind of improvement were you looking for? i mean, other than moving your servers onto a Cray machine...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •