SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    This query hangs the web server

    It looks innocent enough, but apparently I'm structuring something incorrectly.

    SELECT u.firstname, u.lastname, n.nID, n.date, n.priority, n.type, n.sender, n.recipient, n.subject, n.message FROM users u, notifications n WHERE n.sender = u.email order by priority limit 5

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,067
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    There's not much to go on here, but you should at least drop that carthesian product in favor of a JOIN:

    Code MySQL:
    SELECT
       u.firstname
     , u.lastname
     , n.nID
     , n.date
     , n.priority
     , n.type
     , n.sender
     , n.recipient
     , n.subject
     , n.message
     FROM users u
    INNER
     JOIN notifications n
       ON n.sender = u.email
    ORDER
       BY priority
    LIMIT 5

    Please post the SHOW CREATE TABLE for both the users and notificiation tables as well as the result of the EXPLAIN of the query above.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    It looks innocent enough, but apparently I'm structuring something incorrectly.
    you're asking the database engine to retrieve all notifications forall users, and then sort all the rows thus obtained by priority

    is there an index on the priority column? on the sender column?
    rudy.ca | @rudydotca
    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
  •