Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 12 of 12
  1. #1
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Mysql Query Optimize

    Hi All
    Need Help to optimize this query, the result wanted is select the max data in union table tbl_inbox and tbl_outbox group by Remoteaddress....
    and left join with tbl_member for getting name if remoteaddress is in member data.

    uid is unique number in tbl_inbox and tbl_outbox


    the query is succeed, but it takes 1 second, the data of tbl_inbox and tbl_outbox total is about 2600...


    optional:

    wondering if the result of this query include the total count of the remoteaddress data in union tbl_inbox and tbl_outbox, already do separate query for this counter


    PHP Code:
    SELECT t . * , x.memberidx.membernameDATE_FORMATt.indatetime'%d-%c-%Y %H:%i:%s' ) AS indatetime
    FROM 
    (

    SELECT maxuid ) AS uidm.id AS memberidCONCATm.firstnameCONCAT' 'm.middlename ) , CONCAT' 'm.lastname ) ) AS membername
    FROM tbl_member m
    RIGHT JOIN 
    (

    SELECT uidindatetimeremoteaddresstext,
    STATUS FROM tbl_inbox
    UNION ALL SELECT uid
    indatetimeremoteaddresstext,
    STATUS FROM tbl_outbox
    )u ON u.remoteaddress
    IN 
    (
    m.mobilephonenumber1m.mobilephonenumber2m.mobilephonenumber3m.mobilephonenumber4m.fixphonenumber
    )
    WHERE DATEu.indatetime )
    BETWEEN '2001-12-10'
    AND '2011-12-10'
    GROUP BY remoteaddress
    )x
    JOIN 
    (

    SELECT uidindatetimeremoteaddresstext,
    STATUS FROM tbl_inbox
    UNION ALL SELECT uid
    indatetimeremoteaddresstext,
    STATUS FROM tbl_outbox
    )t ON x.uid t.uid
    ORDER BY t
    .uid DESC 

    explain says

    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	PRIMARY 	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Impossible WHERE noticed after reading const table...
    5 	DERIVED 	tbl_inbox 	ALL 	NULL	NULL	NULL	NULL	1183 	
    6 	UNION 	tbl_outbox 	ALL 	NULL	NULL	NULL	NULL	1454 	
    NULL	UNION RESULT 	<union5,6> 	ALL 	NULL	NULL	NULL	NULL	NULL	
    2 	DERIVED 	<derived3> 	ALL 	NULL	NULL	NULL	NULL	2637 	Using where; Using temporary; Using filesort
    2 	DERIVED 	m 	ALL 	NULL	NULL	NULL	NULL	163 	
    3 	DERIVED 	tbl_inbox 	ALL 	NULL	NULL	NULL	NULL	1183 	
    4 	UNION 	tbl_outbox 	ALL 	NULL	NULL	NULL	NULL	1454 	
    NULL	UNION RESULT 	<union3,4> 	ALL 	NULL	NULL	NULL	NULL	NULL
    any response really appreciated, and let me know if other detail is needed... thank you.
    Last edited by mindwin; 12-10-2011 at 01:29 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Ummm...not one of the sub-queries there is able to use a KEY. That's a killer. That means that you really are scanning *ALL* the records every single time.

    I think maybe you need to show us your DB schema. Show all the tables you are using, the relevant fields in each one, and which ones are keys.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Really Thank you for respond !!
    I see, i read many how NULL value on explain is problem on query, but i tried to add index in uid, id and remoteaddress, mobilephonenumber as this field is connect to other table but the explain remain the same.

    here is my table


    Code:
     Tbl Inbox
    
            1 	indatetime 	timestamp 			
    	2 	uid 	bigint(20) 		INDEX <<--this field is unique on tbl_inbox an tbl_outbox for union purpose
    	3 	id 	int(20) 		Primary Key
    	4 	remoteaddress 	varchar(30) 	INDEX
    	5 	text 	varchar(1500) 	
    	6 	smsc 	varchar(20) 	
    	7 	status 	enum('unread', 'read', 'replied') 	
    	8 	detailstatus 	varchar(300) 	
    	9 	portid 	varchar(50) 	
    	10 	responseid 	int(11) 			
    	11 	statusdatetime 	timestamp 		
    
      Tbl outbox
    
            1 	indatetime 	timestamp 			
    	2 	uid 	bigint(20) 		INDEX <<--this field is unique on tbl_inbox an tbl_outbox for union purpose
    	3 	id 	int(20) 		Primary Key
    	4 	remoteaddress 	varchar(30) 	INDEX
    	5 	text 	varchar(1500) 	
    	6 	status 	enum('unread', 'read', 'replied') 	
    	7 	detailstatus 	varchar(300) 	
    	8 	portid 	varchar(50) 	
    	9 	responseid 	int(11) 			
    	10 	statusdatetime 	timestamp 		
    
    Tbl member
           	1 	id 	int(11) 		Primary key
    	2 	indatetime 	timestamp 		
    	3 	firstname 	varchar(50) 	
    	4 	middlename 	varchar(50) 	
    	5 	lastname 	varchar(50) 	
    	6 	gender 
    	7 	mobilephonenumber1 	varchar(20) 	INDEX
    	8 	mobilephonenumber2 	varchar(20) 	INDEX
    	9 	mobilephonenumber3 	varchar(20) 	INDEX
    	10 	mobilephonenumber4 	varchar(20)        INDEX
    	11 	fixphonenumber 	varchar(20) 	INDEX
    Last edited by mindwin; 12-10-2011 at 10:19 AM. Reason: minimalize field for easiest look

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Okay, but you didn't say what fields, if any, you have created indexes on. Other than the primary key which isn't going to help performance for this query.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you old pedant, seems like you're single fighter to answer many thread in this section

    i've edit the post...

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Have to look at this tomorrow. Got home late tonight and it will take a while to analyze it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Okay, let's first run a couple of experiments.

    You can just try this directly from mysql command line or phpmysqladmin tool.

    Code:
    SELECT remoteaddress, MAX(uid) AS maxuid
    FROM Inbox
    WHERE DATE(indatetime ) BETWEEN '2001-12-10' AND '2011-12-10'
    GROUP BY remoteaddress;
    
    SELECT remoteaddress, MAX(uid) AS maxuid
    FROM Outbox
    WHERE DATE(indatetime ) BETWEEN '2001-12-10' AND '2011-12-10'
    GROUP BY remoteaddress;
    Try those two queries separately and tell me how long each takes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    You know, it might help if you put an index on indatetime on the two tables.

    If you do that, then I would change
    Code:
        WHERE DATE(indatetime ) BETWEEN '2001-12-10' AND '2011-12-10'
    to this:
    Code:
        WHERE indatetime >= '2001-12-10' AND indatetime < '2011-12-11'
    MySQL can't use an indexed TIMESTAMP column properly if you apply a function (in this case, the DATE() function) to it. So by converting that to the code shown (notice that the second condition is < one day more than your BETWEEN!) it will use the index on TIMESTAMP if it is there.

    Without that index, MySQL will have to scan *EVERY RECORD* in both the INBOX and OUTBOX tables.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    And I forgot to ask: How many records are returned by your working query?

    If it's several thousand, then I don't think you are going to improve on a one second time.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Here was my thought: MySQL sometimes has better luck optimizing things when you use a VIEW to consolidate multiple SELECTs. Don't ask me why. It's just been my experience.

    So I wouldn't make a huge change to your code. I'd just do this:
    Code:
    CREATE VIEW getMaxuidOfUnion
    AS
       SELECT remoteaddress, MAX(uid) AS maxuid
       FROM Inbox
       WHERE indatetime >= '2001-12-10' AND indatetime < '2011-12-11'
       GROUP BY remoteaddress;
          UNION
       SELECT remoteaddress, MAX(uid) AS maxuid
       FROM Outbox
       WHERE indatetime >= '2001-12-10' AND indatetime < '2011-12-11'
       GROUP BY remoteaddress;
    
    CREATE VIEW getInOutUnion
    AS
        SELECT uid, indatetime, remoteaddress, text, status FROM tbl_inbox
            UNION ALL
        SELECT uid, indatetime, remoteaddress, text, status FROM tbl_outbox;
    
    SELECT t.*, m.memberid, m.membername, DATE_FORMAT( t.indatetime, '%d-%c-%Y %H:%i:%s' ) AS indatetime
    FROM (
        SELECT remoteaddress, MAX(maxuid) AS topuid
        FROM getMaxuidOfUnion 
        GROUP BY remoteaddress ) AS u
    INNER JOIN getInOutUnion AS t
    ON t.uid = u.topuid
    LEFT JOIN members AS m
    ON u.remoteaddress IN (
        m.mobilephonenumber1, m.mobilephonenumber2, m.mobilephonenumber3, m.mobilephonenumber4, m.fixphonenumber
    )
    Again, though, you might well profit from putting an index on that timestamp column.

    Depends a lot on how many total records are in the two tables and how many records are in your final result.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    mindwin (12-11-2011)

  • #11
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Old Pedant, give me a while to try your code and report back

  • #12
    New to the CF scene
    Join Date
    Dec 2011
    Posts
    6
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Here is the explain after applying the code

    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	PRIMARY 	<derived2> 	ALL 	NULL	NULL	NULL	NULL	410 	Using temporary; Using filesort
    1 	PRIMARY 	m 	ALL 	NULL	NULL	NULL	NULL	162 	
    1 	PRIMARY 	<derived5> 	ALL 	NULL	NULL	NULL	NULL	2769 	Using where; Using join buffer
    5 	DERIVED 	tbl_inbox 	ALL 	NULL	NULL	NULL	NULL	1189 	
    6 	UNION 	tbl_outbox 	ALL 	NULL	NULL	NULL	NULL	1580 	
    NULL	UNION RESULT 	<union5,6> 	ALL 	NULL	NULL	NULL	NULL	NULL	
    2 	DERIVED 	<derived3> 	ALL 	NULL	NULL	NULL	NULL	607 	Using temporary; Using filesort
    3 	DERIVED 	tbl_inbox 	ALL 	indatetime 	NULL	NULL	NULL	1189 	Using where; Using temporary; Using filesort
    4 	UNION 	tbl_outbox 	ALL 	indatetime 	NULL	NULL	NULL	1580 	Using where; Using temporary; Using filesort
    NULL	UNION RESULT 	<union3,4> 	ALL 	NULL	NULL	NULL	NULL	NULL
    The query double faster... about 0,5 sec

    About 0,25 per query

    As i use this query twice for paging (show current page and count all data)

    Is there no way to get a better scan data for this query ?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •