SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict ruba's Avatar
    Join Date
    Apr 2005
    Location
    Amman -Jordan
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question order by make my query v.v. slow

    hi every body ,,

    I have this query


    SELECT a.id, a.title, a.alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
    CASE WHEN a.modified = '0000-00-00 00:00:00' THEN a.created ELSE a.modified END as modified,
    a.modified_by, uam.name as modified_by_name,
    CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END as publish_up,
    a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured,
    LENGTH(a.fulltext) AS readmore,
    CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,
    c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,
    CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,
    ua.email AS author_email,

    ( SELECT MAX(contact.id) AS id FROM blah__contact_details AS contact WHERE contact.published = 1 AND contact.user_id = a.created_by) as contactid,

    parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,

    ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published,
    CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published

    FROM
    blah__content AS a
    LEFT JOIN blah__content_frontpage AS fp ON fp.content_id = a.id
    LEFT JOIN blah__categories AS c ON c.id = a.catid
    LEFT JOIN blah__users AS ua ON ua.id = a.created_by
    LEFT JOIN blah__users AS uam ON uam.id = a.modified_by
    LEFT JOIN blah__categories as parent ON parent.id = c.parent_id
    LEFT JOIN blah__content_rating AS v ON a.id = v.content_id
    LEFT OUTER JOIN (SELECT cat.id as id FROM blah__categories AS cat JOIN blah__categories AS parent ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = 'com_content' AND parent.published != 1 GROUP BY cat.id ) AS badcats
    ON
    badcats.id = c.id

    WHERE
    CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
    AND (a.catid = 79 OR a.catid IN ( SELECT sub.id FROM blah__categories as sub INNER JOIN blah__categories as this ON sub.lft > this.lft AND sub.rgt < this.rgt WHERE this.id = 79))
    AND (a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2014-05-31 08:44:20')
    AND (a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2014-05-31 08:44:20')

    ORDER BY CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END DESC , a.created LIMIT 0, 30

    this query take more than 8 sec..
    when I remove the order by .. it take 0.0141 sec

    I triend to change the order by to "ORDER BY a.id " and it take around 5 sec

    what I should do ?? why order by make my query v.v. slow

    I will be very thankfull for any help
    Open Blocked website
    Open Blocked Website
    Knowledge Is Knowing That A Tomato Is A Fruit,
    Wisdom Is Not Putting It In A Fruit Salad.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    change the part in red here --

    SELECT a.id, a.title, a.alias, a.introtext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
    CASE WHEN a.modified = '0000-00-00 00:00:00' THEN a.created ELSE a.modified END as modified,
    a.modified_by, uam.name as modified_by_name,
    CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END as publish_up, ...

    to --

    COALESCE(NULLIF(a.publish_up,'0000-00-00 00:00:00'),a.created) AS new_publish_up

    and then use new_publish_up in your ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict ruba's Avatar
    Join Date
    Apr 2005
    Location
    Amman -Jordan
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for reply ..

    but the query still slow ..
    also I found this error

    Incorrect key file for table 'mysql/tmp/#sql_xxx_x.MYI'; try to repair it SQL=

    I repair the tables using phpmyadmin .. but the query still slow
    Open Blocked website
    Open Blocked Website
    Knowledge Is Knowing That A Tomato Is A Fruit,
    Wisdom Is Not Putting It In A Fruit Salad.

  4. #4
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Create an index on the column which you are using in the order by clause and see the performance.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vpalukuru9 View Post
    Create an index on the column which you are using in the order by clause and see the performance.
    i'm not sure you noticed, but the ORDER BY column isn't a column, it's the result of a function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Nov 2013
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Its my mistake. I just saw the columns in the select clause and gave suggestion.

  7. #7
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ruba View Post
    what I should do ?? why order by make my query v.v. slow
    The query is very messy (in my opinion), and you would probably benefit from separating it into several queries instead of one big one (considering resources/speed).

    What I would do is separate parts of this into a temporary memory table, where you just run part of the above query without the order by. The key is that you will contain the primary keys required to find the data you need to have in the sorted query. In addition you should put the a.created and a.publish_up columns into a single column in the temporary table (have an index or composite key with the column depending on your table), then after you have done the insert. You just do the select with the order by from the temporary table (joining any required tables).

    Many times it is faster running several smaller/simple queries than one large/complex query, so when you run into slow queries, check indexes, and if that does not help, consider the query itself (i.e. can it be written more effectively), then if it can be divided up into several queries, or temporary tables to speed it up.

    In addition, if you use the "LIMIT 0, 30" as pagination, you should reconsider that on larger tables as it get slower the more records you have, since it reads every row up to the ones you want.


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
  •