Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 3 of 3
  1. #1
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    Hi all,

    I'm trying to optimize queries on a website, i currently have these 4 queries running and i would like to make them (if possible) into one query.

    At present the only difference is selecting articles from a section and varrying the LIMIT clause.

    Can anyone suggest a more efficent way to do this?

    I have thought about doing a SELECT * but that would return 2500 records ( i only want about 30 odd (add the limit numbers))
    I have also considered a UNION, but i am unsure how that would work, i have expieremented with UNION clause but cannot seem to get the ORDER BY field LIMIT 0,6 part of it going. i need to limit each portion of the UNION respectively.

    Any help is appreciated, i think im going down the right track:

    Here are the queries used:

    Code:
    SELECT * FROM Article__Index WHERE section = 'news' AND date_time < NOW() ORDER BY date_time DESC LIMIT 0, 9
    UNION
    SELECT * FROM Article__Index WHERE section = 'features' AND date_time < NOW() ORDER BY date_time DESC LIMIT 0, 6
    UNION
    SELECT * FROM Article__Index WHERE section = 'reviews' AND date_time < NOW() ORDER BY date_time DESC LIMIT 0, 6
    UNION
    SELECT * FROM Article__Index WHERE section = 'previews' AND date_time < NOW() ORDER BY date_time DESC LIMIT 0, 6

  2.  

  3. #2
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    Before I forget, put an index on the section and date_time columns.

    It looks simple at first glance, but then I noticed the limits and what you're trying to do: get the 10 most recent news/features articles along with the seven most recent reviews/previews for a total of 32 rows. I do believe if you just put indices on the two columns that performance won't be an issue. It's still just one query technically.

    If you're using MySQL 5, consider making a view from that query and querying the view instead.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  4. #3
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    thanks for the quick reply.

    After reading your answer i have re-thinked my problem.

    I have added indices in a couple of places thanks. (its not like me to forget indices!)

    These are the actual queries used to get me the data (4 queries) each takes about

    Code:
    SELECT * FROM Article__Index WHERE section = 'news' AND active = '1' AND is_published = '1' AND date_time < '1161665755' ORDER BY date_time DESC LIMIT 0, 9;
    #Done: 0.009s
    
    SELECT * FROM Article__Index WHERE section = 'features' AND active = '1' AND is_published = '1' AND date_time < '1161665755' ORDER BY date_time DESC LIMIT 0, 6;
    #Done: 0.002s
    
    SELECT * FROM Article__Index WHERE section = 'reviews' AND active = '1' AND is_published = '1' AND date_time < '1161665755' ORDER BY date_time DESC LIMIT 0, 6;
    #Done: 0.002s
    
    SELECT * FROM Article__Index WHERE section = 'previews' AND active = '1' AND is_published = '1' AND date_time < '1161665755' ORDER BY date_time DESC LIMIT 0, 6;
    #Done: 0.002s
    
    #Total Done 4 (0.015s)
    It is probably doesnt matter, but the limitations foreach query i call upon are caclulated on the fly and are sometimes higher/lower.

    so in summary, lol:

    i want to get the 9 latest news articles
    i want to get the 6 latest features
    i want to get the 6 latest reviews
    i want to get the 6 latest previews
    i want to do the above in one query.

    I have tried to do a UNION, but i seem to not be able to keep the ORDER-ing and LIMIT-ing i want, UNION guru's ?

    Thanks


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 07:02 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com