SitePoint Sponsor

User Tag List

Results 1 to 20 of 20
  1. #1
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to optemise the query having group by on two colomun

    i have one table say A

    +------------+--------------+----------------+
    | value1 | value 2 | value 3 |
    +------------+--------------+----------------+
    | 5 | 4adb83c1d71b | 1 |
    | 5 | 44fcb93589fe | 2 |
    | 5 | 4182b6e2100c | 1 |

    | 6 | 4bfd8db46ab1 | 1 |

    | 7 | 4b0f87c68808 | 1 |
    | 7 | 413fa4646dfe | 2 |
    | 7 | 4585b5671b68 | 3 |
    | 7 | 4486abc0ddce | 2 |
    | 7 | 4b01b048f07c | 5 |
    | 7 | 4cebb754ff21 | 2 |
    +------------+--------------+----------------+

    Now i need an optemise query that will give number of duplicate in value 3 colomun for each value 1 colomun

    mean i need a result as

    +------------+--------------+----------------+
    | value1 | value 3 | count |
    +------------+--------------+----------------+
    | 5 | 1 | 2 |
    | 6 | 1 | 1 |
    | 7 | 2 | 3 |
    +------------+--------------+----------------+


    mean
    value 1(5) have value 3(1) repeate 2 time
    value 1(6) have value 3(1) repeate 1 time
    value 1(7) have value 3(2) repeate 3 time

    for this i write a query but that query is not optemise so if have any better solution for query.

    select value1,value3,count(value1) as ct from A where value1>0 and value1 != '' group by value1,value2 having ct >1 limit 3;

    where value 1 and value 3 are indexed

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    ALTER TABLE a ADD INDEX(value1,value2)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select value1,value3,count(value1) as ct from A where value1>0 and value1 != '' group by value1,value3 having ct >1 limit 3;

    value 1 and value 3 are already index then also it scan the whole row

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    ALTER TABLE a ADD INDEX(value1,value3)

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb

    Quote Originally Posted by r937 View Post
    ALTER TABLE a ADD INDEX(value1,value3)

    value1 and value 3 are already index in table so this does not work

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sure it does

    i am not suggesting separate indexes on those columns individually, i am suggesting a single composite index on both columns
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    +----+-------------+---------------------+-------+-----------------------------------------------------+--------------+---------+------+---------+-----------------------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------------------+-------+-----------------------------------------------------+--------------+---------+------+---------+-----------------------------------------------------------+
    | 1 | SIMPLE | A | range | value1_3 | value1_3 | 8 | NULL | 3124145 | Using where; Using index; Using temporary; Using filesort |
    +----+-------------+---------------------+-------+-----------------------------------------------------+--------------+---------+------+---------+-----------------------------------------------------------+

    so as we see from here value1_3 is index of (value1 and value3)

    Still expensive

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by phpgurullc View Post
    Still expensive
    compared to what? you're reading three million rows!!!
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    3 million rows entries are in table A, so when we explain this query it give us this result
    +----+-------------+---------------------+-------+-----------------------------------------------------+--------------+---------+------+---------+-----------------------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+---------------------+-------+-----------------------------------------------------+--------------+---------+------+---------+-----------------------------------------------------------+
    | 1 | SIMPLE | A | range | value1_3 | value1_3 | 8 | NULL | 3124145 | Using where; Using index; Using temporary; Using filesort |
    +----+-------------+---------------------+-------+-----------------------------------------------------+--------------+---------+------+---------+-----------------------------------------------------------+

    So its scanning the whole table

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    actually, it's not accessing the table at all, just the index

    you're using an ORDER BY clause, right?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    ALTER TABLE a ADD INDEX(value1,value3)

    Quote Originally Posted by r937 View Post
    actually, it's not accessing the table at all, just the index

    you're using an ORDER BY clause, right?
    its group by clause

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    so there is no ORDER BY clause?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dear Just go through this query,we are not using order by we are using group by

    select value1,value3,count(value1) as ct from A where value1>0 and value1 != '' group by value1,value2 having ct >1 limit 3;

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by phpgurullc View Post
    select value1,value3,count(value1) as ct from A where value1>0 and value1 != '' group by value1,value2 having ct >1 limit 3;
    there's your problem... you're grouping by something, but selecting something else
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there's your problem... you're grouping by something, but selecting something else
    sorry its typo error
    select value1,value3,count(value1) as ct from A where value1>0 and value1 != '' group by value1,value3 having ct >1 limit 3;

  16. #16
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    okay, i don't think i can keep dancing around the real issue here with fake table and column names

    is there any chance you can show your actual table and actual query?

    do a SHOW CREATE TABLE for me please
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  17. #17
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Table Structure is as follows


    | gallery_event_image | CREATE TABLE `gallery_event_image` (
    `gallery_id` bigint(20) unsigned NOT NULL,
    `image_id` varchar(20) NOT NULL,
    `image_position` smallint(5) unsigned DEFAULT NULL,
    UNIQUE KEY `gallery_id_2` (`gallery_id`,`image_id`),
    KEY `gallery_id` (`gallery_id`),
    KEY `image_id` (`image_id`),
    KEY `image_position` (`image_position`),
    KEY `gallery_id_3` (`gallery_id`,`image_position`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |


    and real query


    select gallery_id,image_position,count(gallery_id) as ct from gallery_event_image WHERE image_position < 255 and gallery_id!=0 and gallery_id != '' group by gallery_id,image_position having ct >1 order by ct asc limit 500;

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    well, whaddya know... there ~was~ an ORDER BY clause after all

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Member
    Join Date
    Mar 2010
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    there's your problem... you're grouping by something, but selecting something else
    Quote Originally Posted by r937 View Post
    well, whaddya know... there ~was~ an ORDER BY clause after all

    ok, so how can we solve it

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you can't solve it

    if you want the lowest 500 counts, you're going to have to compute all the counts, right?

    that's what using temporary and using filesort mean -- each gallery_id/image_position combination is counted, the results are placed into a temporary file, and then this file is sorted into ascending sequence by the count

    that's what you asked for, and your query is as optimized as it's going to get
    r937.com | rudy.ca | 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
  •