CODEWITHSUNDEEP
mysqlunion
John
John

Reputation: 23

How can I simplify this MySQL query when using a UNION ALL?

This query works.. for querying 50 mysql databases at once and returning the most recent 10 results, but.. is there a way to not query the entire database of each state (only 4 states listed in my example), just get the top 10 (by timeStamp desc) and make this query far more efficient? 

$query = "    
SELECT imgURLThumb, timeStamp, title2, state, postId, title
FROM (
  SELECT imgURLThumb,timeStamp, title2, state, postId, title FROM db_washington.md_postings UNION ALL 
  SELECT imgURLThumb,timeStamp, title2, state, postId, title FROM db_westvirginia.md_postings UNION ALL 
  SELECT imgURLThumb,timeStamp, title2, state, postId, title FROM db_wisconsin.md_postings UNION ALL 
  SELECT imgURLThumb,timeStamp, title2, state, postId, title FROM db_wyoming.md_postings 

) allposts where imgURLThumb <> 'images/nopicture.png' order by timeStamp DESC LIMIT 0 , 10";

Upvotes: 1

Views: 143

Answers (1)

Mark Byers
Mark Byers

Reputation: 838086

You should redesign your database so that all the postings are in a single database to make this query much easier to write. It looks like a bad design to have a database for each state. You should instead have a single table md_postings for all postings, where one of the fields is State.

If that's not possible then I think you have a trade-off:

  • Concise and readable SQL or
  • Good performance.

If you want better performance try this:

SELECT imgURLThumb, timeStamp, title2, state, postId, title
FROM (
    (SELECT imgURLThumb,timeStamp, title2, state, postId, title
    FROM db_washington.md_postings
    WHERE imgURLThumb <> 'images/nopicture.png'
    ORDER BY timeStamp DESC
    LIMIT 10)

    UNION ALL

    (SELECT imgURLThumb,timeStamp, title2, state, postId, title
    FROM db_westvirginia.md_postings
    WHERE imgURLThumb <> 'images/nopicture.png'
    ORDER BY timeStamp DESC
    LIMIT 10)

    UNION ALL

    (SELECT imgURLThumb,timeStamp, title2, state, postId, title
    FROM db_wisconsin.md_postings
    WHERE imgURLThumb <> 'images/nopicture.png'
    ORDER BY timeStamp DESC
    LIMIT 10)

    UNION ALL

    (SELECT imgURLThumb,timeStamp, title2, state, postId, title
    FROM db_wyoming.md_postings
    WHERE imgURLThumb <> 'images/nopicture.png'
    ORDER BY timeStamp DESC
    LIMIT 10)
) AS allposts
WHERE imgURLThumb <> 'images/nopicture.png'
ORDER BY timeStamp DESC LIMIT 10

Upvotes: 1

Related Questions