...

View Full Version : Mysql Query Optimize



mindwin
12-10-2011, 02:24 AM
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.

Old Pedant
12-10-2011, 03:35 AM
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.

mindwin
12-10-2011, 07:06 AM
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

Old Pedant
12-10-2011, 10:19 AM
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.

mindwin
12-10-2011, 11:22 AM
Thank you old pedant, seems like you're single fighter to answer many thread in this section :D

i've edit the post...

Old Pedant
12-11-2011, 07:24 AM
Have to look at this tomorrow. Got home late tonight and it will take a while to analyze it.

Old Pedant
12-11-2011, 09:30 PM
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.

Old Pedant
12-11-2011, 09:41 PM
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.

Old Pedant
12-11-2011, 09:48 PM
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.

Old Pedant
12-11-2011, 09:51 PM
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.

mindwin
12-11-2011, 11:04 PM
Thanks Old Pedant, give me a while to try your code and report back

mindwin
12-11-2011, 11:34 PM
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 ?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum