#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    6
    Rep Power
    0

    Slow querys, how to improve them?


    Hello folks,

    Could anyone point me out how to achieve as fast querys as possible?
    My database (MySQL 5.6).
    I have one main table where I store the data most accessed. And have four other tables containing more detailed stuff I can get out with joins with the main table.

    After getting som data in to the system the querys are getting really slow. >30 secs in some. (Even only query the main table)

    Ive added the columns used in my querys to index but the problem persists. Could it be the server used is to weak?
    (Windows 2003 Server R2, 2Ghz and 4GB Ram)

    Really appreciate any advice on how to troubleshoot this.
    Ive looked into the slow log, and it shows the stored routines im using.

    Reagards Pzr
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,449
    Rep Power
    1751
    A good place to look is at the EXPLAIN of a query that runs slow, that may give a hint of where to look deeper. If you can show us that then some more meaningful help may be forthcoming. It will also help to see a SHOW CREATE TABLE for each table involved in the query.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    6
    Rep Power
    0
    Originally Posted by SimonJM
    A good place to look is at the EXPLAIN of a query that runs slow, that may give a hint of where to look deeper. If you can show us that then some more meaningful help may be forthcoming. It will also help to see a SHOW CREATE TABLE for each table involved in the query.
    Ok,
    Heres a "explain" of my first query.
    It takes approx 25 - 35 secs to complete.


    id select_type table type possible_keys key key_len ref rows Extra
    1 SIMPLE ct ALL 290708 Using where; Using temporary; Using filesort
    1 SIMPLE x eq_ref PRIMARY,00020003,studyDate,studyAge PRIMARY 302 rc1.ct.sop 1 Using where

    What do you think? /Pzr
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by Pzr
    What do you think? /Pzr
    i think you should also show the query that this EXPLAIN is for, along with the results of the SHOW CREATE TABLEs for your tables
    Code:
    id  select_type  table  type    possible_keys                        key     key_len  ref           rows    Extra
    1   SIMPLE       ct     ALL                                                                        290708   Using where; Using temporary; Using filesort
    1   SIMPLE       d      eq_ref  PRIMARY,00020003,studyDate,studyAge  PRIMARY   302    rc1.ct.sop        1   Using where
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    i think you should also show the query that this EXPLAIN is for, along with the results of the SHOW CREATE TABLEs for your tables
    Code:
    id  select_type  table  type    possible_keys                        key     key_len  ref           rows    Extra
    1   SIMPLE       ct     ALL                                                                        290708   Using where; Using temporary; Using filesort
    1   SIMPLE       x      eq_ref  PRIMARY,00020003,studyDate,studyAge  PRIMARY   302    rc1.ct.sop        1   Using where
    Hmm ok.

    Heres the query:

    my query Code:
    select count(distinct(d.00020003)) as antal,
    x.00400254 as type,
    x.00100020 as number,
    x.00400241 as aet
    from xml as x
    where x.00400244 between '20090101' and '20140101'
    and x.age between '0' and '120'
    and x.00400254 != 'NULL' 
    and x.00400252 = 'COMPLETED'
    and FIND_IN_SET(d.publicname,'LAR 1,LAR 2,LAR 3,LAR 4')
    group by x.00400254;



    Heres my main table:


    Main table Code:
    CREATE TABLE `xml` (
      `int` int(11) NOT NULL AUTO_INCREMENT,
      `blob` text COLLATE utf8_unicode_ci,
      `gender` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `age` decimal(10,0) DEFAULT NULL,
      `publicname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `barcolor` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `stot` decimal(10,4) DEFAULT NULL,
      `00020001` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020002` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020003` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
      `00020010` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020012` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020013` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080005` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080050` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080060` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080100` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081032` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081120` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100010` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100020` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100030` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100040` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00181110` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00200010` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400007` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400241` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400242` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400243` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400244` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400245` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400250` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400251` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400252` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400253` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400254` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400260` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400270` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400301` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400306` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0040030E` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400310` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0018115E` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400300` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400302` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400340` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0040030F` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081050` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00082229` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400303` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00408302` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400255` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400316` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400318` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400321` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400324` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080016` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080018` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100021` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400281` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`00020003`),
      UNIQUE KEY `00020003` (`00020003`),
      UNIQUE KEY `int` (`int`),
      KEY `studyDate` (`00400244`),
      KEY `studyAge` (`studyage`),
      KEY `gender` (`gender`),
      KEY `publicname` (`publicname`),
      KEY `barcolor` (`barcolor`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100755 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


    Any ideas? / Pzr
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,449
    Rep Power
    1751
    Comments apart from "wtf is that naming 'standard?"?

    I may be missing something but where is the table (maybe) aliased as d which is referenced in the FIND_IN_SET(...) and the SELECT COUNT(...)?

    Your explain also refers to a table of ct that does not appear on the query. I am doubly confused!

    Generally mySQL permits the use of one index per table in a query, and you have lots of columns being referenced in the WHERE and each (I think) of them has an index on that column alone. A compound index may help here.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    what simon said...
    - d table missing
    - ct table missing
    - compound index may help

    i'd like to add...
    - applying a function like FIND_IN_SET makes index useless
    - non-aggregates in SELECT clause that are not included in GROUP BY will have indeterminate values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    what simon said...
    - d table missing
    - ct table missing
    - compound index may help

    i'd like to add...
    - applying a function like FIND_IN_SET makes index useless
    - non-aggregates in SELECT clause that are not included in GROUP BY will have indeterminate values
    Sorry guys.

    I seem to have messed some up here.
    Heres what was asked of me to be able to advice:


    explain of query Code:
    "id"	"select_type"	"table"	"type"	"possible_keys"	"key"	"key_len"	"ref"	"rows"	"Extra"
    "1"	"SIMPLE"	"ct"	"ALL"	\N	\N	\N	\N	"272436"	"Using where; Using temporary; Using filesort"
    "1"	"SIMPLE"	"x"	"eq_ref"	"PRIMARY,00020003,studyDate,studyAge"	"PRIMARY"	"302"	"rc1.ct.sop"	"1"	"Using where"


    and the query itself:

    the query Code:
    explain
    select count(distinct(x.00020003)) as antal,
    x.00400254 as type,
    x.00100020 as number,
    x.00400241 as aet
    from xml as x
    join ct 
    on x.00020003 = ct.sop
    where x.00400244 between '20130101' and '20140101'
    and x.studyage between '0' and '120'
    and x.00400252 = 'COMPLETED' 
    and FIND_IN_SET(x.publicname,'LAR 1,LAR 2,LAR 3,LAR 4') 
    group by x.00400254;


    and finally maintable code:

    maintable xml Code:
    CREATE TABLE `xml` (
      `int` int(11) NOT NULL AUTO_INCREMENT,
      `blob` text COLLATE utf8_unicode_ci,
      `gender` char(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `age` decimal(10,0) DEFAULT NULL,
      `publicname` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `barcolor` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `stot` decimal(10,4) DEFAULT NULL,
      `00020001` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020002` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020003` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
      `00020010` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020012` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00020013` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080005` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080050` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080060` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080100` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081032` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081120` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100010` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100020` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100030` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100040` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00181110` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00200010` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400007` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400241` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400242` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400243` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400244` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400245` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400250` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400251` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400252` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400253` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400254` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400260` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400270` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400301` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400306` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0040030E` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400310` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0018115E` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400300` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400302` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400340` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0040030F` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081050` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00082229` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400303` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00408302` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400255` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400316` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400318` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400321` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400324` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080016` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00080018` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00100021` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00400281` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      PRIMARY KEY (`00020003`),
      UNIQUE KEY `00020003` (`00020003`),
      UNIQUE KEY `int` (`int`),
      KEY `studyDate` (`00400244`),
      KEY `studyAge` (`studyage`),
      KEY `gender` (`gender`),
      KEY `publicname` (`publicname`),
      KEY `barcolor` (`barcolor`)
    ) ENGINE=InnoDB AUTO_INCREMENT=100755 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


    and table ct:


    table ct Code:
    "Table"	"Create Table"
    "ct"	"CREATE TABLE `ct` (
      `int_ct` int(11) NOT NULL AUTO_INCREMENT,
      `protocol` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `sop` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00181160` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00181150` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00188151` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0018115A` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00180060` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `0008103E` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00181030` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081050` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `00081070` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `typ` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `kO` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `msa` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `CTP` decimal(10,4) DEFAULT NULL,
      `Size` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
      `Depth` decimal(10,4) DEFAULT NULL,
      `Pm` varchar(50) COLLATE utf8_unicode_ci DEFAULT '0',
      KEY `int` (`int_ct`)
    ) ENGINE=InnoDB AUTO_INCREMENT=295567 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"


    I think I might have found the thing slowing stuff up. The inno_table_size was only 450M
    I changed it up to 1.2G and now everything runs much faster. instead of >30sec I only get 10sec.
    Will try putting the DB on a separate machine with 20GB tonight.


    - compound index may help

    i'd like to add...
    - applying a function like FIND_IN_SET makes index useless
    - non-aggregates in SELECT clause that are not included in GROUP BY will have indeterminate values

    Could you maby point out links for me to read about this or explain more in detail? Why is find_in_set bad? Is there a better way to do this?
    Any tip appreciated in how to make the query better.

    Any ideas? (Besides I messed up showing correct info here?)
    /Pzr
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,449
    Rep Power
    1751
    No way am I an expert, but cannot find a reference (from a very quick check ) of a parameter inno_table_size so cannot think of why it's help spee,d unless it's a memory/cache parameter allowing more/better use of RAM.
    As an aside you are happy that the values returned for type, number and aet will be random ones from those that exits within the grouping of the 00400254 column (just spotted Rudy already mentioned that!)

    The main issue looks to be the lack of index on ct.sop.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    6
    Rep Power
    0
    Okay, Thats because there are multiple sops that are identical in ct.sop. Can i make that an index anyway?
    (What I mean is that there are multiple rows there pointing at an single row in column xml.sop)

    Code:
    As an aside you are happy that the values returned for type, number and aet will be random ones from those that exits within the grouping of the 00400254 column (just spotted Rudy already mentioned that!)


    So what would you propose for me to do here?

    /Pzr
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,449
    Rep Power
    1751
    The link between x.00020003 and ct.sop used by the JOIN will return one result row for each row in ct that has a sop value that matches the value in the x.00020003 column. So, if when processing the query mySQL 'sees' the value of "QAZ" in x.00020003 and there are 10 rows in the ct tabel that have the value of "QAZ" in the sop column then you will get rows rows in the result set. Having a lot of different values in a column is a good reason to have an index (though that is not a 'proper' reason) on that column - assumning you are going to be querying the values in that column, which you, in effect, are by using that column in a JOIN condition.

    As for the random results due to the GROUP BY clause - it has been explained better by others, but think of the addresses in a town. GROUP BY street name. Now, tell me the house number for each street that you'd get if you asked for that column to be shown.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  22. #12
  23. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,201
    Rep Power
    4279
    Originally Posted by SimonJM
    ...think of the addresses in a town. GROUP BY street name. Now, tell me the house number for each street that you'd get if you asked for that column to be shown.
    these examples are easy to construct
    Code:
    SELECT classroom
         , MAX(student_marks) AS highest_mark
         , student_last_name
      FROM classes
    GROUP
        BY classroom
    which student's name gets displayed? hint: it is ~not~ necessarily the student who received the highest mark

    google "GROUP BY with hidden columns"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    6
    Rep Power
    0
    Originally Posted by r937
    these examples are easy to construct
    Code:
    SELECT classroom
         , MAX(student_marks) AS highest_mark
         , student_last_name
      FROM classes
    GROUP
        BY classroom
    which student's name gets displayed? hint: it is ~not~ necessarily the student who received the highest mark

    google "GROUP BY with hidden columns"
    Ok guys.
    Thanks! =)

    I put on my noob-hat.
    Its great to have help like this.

    /Pzr

IMN logo majestic logo threadwatch logo seochat tools logo