SQL Update

Just going to post the new changes I did because Sender/Recipient is redundant with IsIncoming. I changed it to use ExternalAddress and indexed it.

private static final String DATABASE_CREATE =
  "CREATE TABLE Messages "
  + "("
    + "MessageID INTEGER PRIMARY KEY AUTOINCREMENT, "
    + "ProviderID INTEGER NOT NULL, "
    + "MessageStatusID UNSIGNED INTEGER NOT NULL, "
    + "IsIncoming BIT NOT NULL, "
    + "InternalAddress NVARCHAR(128) NULL, "
    + "ExternalAddress VARCHAR(128) NOT NULL, "
    + "CreationDateTime DATETIME NOT NULL, "
    + "LastSendAttemptDateTime DATETIME NULL, "
    + "CompletionDateTime DATETIME NULL, "
    + "MessageText TEXT NOT NULL, "
    + "ExtraData BLOB NULL, "
    + "ExtraDataTypeID UNSIGNED INTEGER NULL, "
    + "ImportMessageID NVARCHAR(40) NULL, "
    + "ImportConversationID NVARCHAR(40) NULL, " 
    + "IsRead BIT NOT NULL" 
  + ")";

private static final String DATABASE_INDEXES_CREATE =
  "CREATE INDEX IX_ProviderID_ExternalAddress_CreationDateTime ON MESSAGES "
  + "("
    + "ProviderID ASC, "
    + "ExternalAddress ASC,"      
    + "CreationDateTime ASC" 
  + ")";
  
String rawQuery = ""
  + "SELECT "
  + "  M2.MessageID, "
  + "  M2.ProviderID, "
  + "  M2.MessageStatusID, "
  + "  M2.IsIncoming, "
  + "  M2.InternalAddress, "
  + "  M2.ExternalAddress, "
  + "  M2.CreationDateTime, "
  + "  M2.LastSendAttemptDateTime, "
  + "  M2.CompletionDateTime, "
  + "  M2.MessageText, "
  + "  M2.ExtraData, "
  + "  M2.ExtraDataTypeID, "
  + "  M2.ImportMessageID, "
  + "  M2.ImportConversationID, "
  + "  M2.IsRead, "
  + "  LastMessageData.ProviderID, "
  + "  LastMessageData.ExternalAddress "
  + "FROM "
  + "  ( "
  + "    SELECT "
  + "      M1.ProviderID, "
  + "      M1.ExternalAddress, "
  + "      MAX(CreationDateTime) [LastMessageTime] "
  + "    FROM "
  + "      Messages M1 "
  + "    GROUP BY "
  + "      M1.ProviderID, "
  + "      M1.ExternalAddress "
  + "  ) AS LastMessageData "
  + "  LEFT JOIN Messages M2 ON  "
  + "    M2.CreationDateTime = LastMessageData.LastMessageTime "
  + "    AND M2.ExternalAddress = LastMessageData.ExternalAddress "
  + "    AND M2.ProviderID = LastMessageData.ProviderID "
  + "ORDER BY                   "
  + "  LastMessageData.ProviderID, "
  + "  LastMessageData.ExternalAddress,                "
  + "  M2.MessageID";

I don’t think there’s any theoretical way to make this faster. You’re all welcome to try, and I’ll patch it in.

One thought on “SQL Update

  1. Dentist Philippines

    Attractive section of content. I just stumbled upon your blog and
    in accession capital to assert that I acquire actually enjoyed account your blog
    posts. Any way I’ll be subscribing to your feeds and even
    I achievement you access consistently quickly.

    Reply

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>