Help speed up query/table

Discussion in 'MySQL' started by renlok, Aug 17, 2009.

0
  1. #1
    I have the following query and its causing the mysql server to run really slowly i would like it if someone could help me optimise it or the table

    well this is the query
    SELECT T.tag, COUNT(T.bId) AS bCount FROM sc_bookmarks2tags AS T, sc_bookmarks AS B WHERE B.bId = T.bId AND B.bStatus = 0 GROUP BY T.tag ORDER BY bCount DESC, tag
    Code (markup):

     
    Last edited: Aug 17, 2009
    renlok, Aug 17, 2009 IP
  2. phones2me

    phones2me Peon

    Messages:
    35
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    it may be possible to speed up the query execution, but we would need to know more about the setup of your database. can you post the schema please
     
    phones2me, Aug 17, 2009 IP
  3. p4n4d0l

    p4n4d0l Peon

    Messages:
    51
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Have you already created index at your table?
     
    p4n4d0l, Aug 19, 2009 IP
  4. premiumscripts

    premiumscripts Peon

    Messages:
    1,062
    Likes Received:
    48
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Run this in phpmyadmin: 

    EXPLAIN SELECT T.tag, COUNT(T.bId) AS bCount FROM sc_bookmarks2tags AS T, sc_bookmarks AS B WHERE B.bId = T.bId AND B.bStatus = 0 GROUP BY T.tag ORDER BY bCount DESC, tag
    Code (markup):
    And post the results (or a screenshot)
     
    premiumscripts, Aug 19, 2009 IP
  5. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #5
    The problem is that you are ordering by the COUNT, which cannot be indexed. Probably not much you can do to speed this up as is.
     
    jestep, Aug 20, 2009 IP