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


SELECT t . * , x.memberid, x.membername, DATE_FORMAT( t.indatetime, '%d-%c-%Y %H:%i:%s' ) AS indatetime
FROM (

SELECT max( uid ) AS uid, m.id AS memberid, CONCAT( m.firstname, CONCAT( ' ', m.middlename ) , CONCAT( ' ', m.lastname ) ) AS membername
FROM tbl_member m
RIGHT JOIN (

SELECT uid, indatetime, remoteaddress, text,
STATUS FROM tbl_inbox
UNION ALL SELECT uid, indatetime, remoteaddress, text,
STATUS FROM tbl_outbox
)u ON u.remoteaddress
IN (
m.mobilephonenumber1, m.mobilephonenumber2, m.mobilephonenumber3, m.mobilephonenumber4, m.fixphonenumber
)
WHERE DATE( u.indatetime )
BETWEEN '2001-12-10'
AND '2011-12-10'
GROUP BY remoteaddress
)x
JOIN (

SELECT uid, indatetime, remoteaddress, text,
STATUS FROM tbl_inbox
UNION ALL SELECT uid, indatetime, remoteaddress, text,
STATUS FROM tbl_outbox
)t ON x.uid = t.uid
ORDER BY t.uid DESC


explain says

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.

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.

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


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

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.

Thank you old pedant, seems like you're single fighter to answer many thread in this section :D

i've edit the post...

Have to look at this tomorrow. Got home late tonight and it will take a while to analyze it.

Okay, let's first run a couple of experiments.

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


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.

You know, it might help if you put an index on indatetime on the two tables.

If you do that, then I would change
WHERE DATE(indatetime ) BETWEEN '2001-12-10' AND '2011-12-10'

to this:
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.

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.

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:

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.

Thanks Old Pedant, give me a while to try your code and report back

Here is the explain after applying the 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 ?