Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > Linux Forums > Server Operation

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 30th March 2010, 11:40
voltron81 voltron81 is offline
Senior Member
 
Join Date: Sep 2009
Posts: 292
Thanks: 1
Thanked 4 Times in 3 Posts
Default A mysql query that is taking too much to be executed(and it shouldn't)

Hi to everybody.
I'm realizing a mailserver with a not common configuration...
Anyway I've a problem with a mysql query, that is taking too much time to be executed...

Basically the two tables involved in that query are:
mail_db(id(key),uidl,date,user_id,fetched)
mail_temp(id(key),user_id,msg_id,uidl,size)

and the query is this one:
SELECT * FROM mail_db WHERE mail_db.user_id = 21 AND mail_db.uidl NOT IN (SELECT uidl FROM mail_temp WHERE user_id = 21);

I'm sure that I'm wrong in something and that's why is taking too much to be executed...
Suggestions?
thanks
Michele
Reply With Quote
Sponsored Links
  #2  
Old 30th March 2010, 12:24
Kalotus Kalotus is offline
Junior Member
 
Join Date: Mar 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ciao voltron81,
can you supply the output of the following?

EXPLAIN EXTENDED SELECT * FROM mail_db WHERE mail_db.user_id = 21 AND mail_db.uidl NOT IN (SELECT uidl FROM mail_temp WHERE user_id = 21);

Please refer to this for reference:

http://dev.mysql.com/doc/refman/5.0/...g-explain.html

Cheers

--
Kamal
Reply With Quote
  #3  
Old 30th March 2010, 12:35
voltron81 voltron81 is offline
Senior Member
 
Join Date: Sep 2009
Posts: 292
Thanks: 1
Thanked 4 Times in 3 Posts
Default

Hi Kamal,
thanks for your reply

This is the output:

[IMG][/IMG]

Next time that you're in London I've to pay you a beer...

Cheers
Michele
Reply With Quote
  #4  
Old 30th March 2010, 12:58
Kalotus Kalotus is offline
Junior Member
 
Join Date: Mar 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ciao Michele,
you already paid me one so you're fine!
The query is doing FTS (Full Table Scan) on both tables, which is not good (you can see it from type = ALL).
Do you have any indexes on the tables? Can you show them?
I would say you need at least one index on mail_db.user_id and one on mail_temp.user_id.

Cheers

--
Kamal
Reply With Quote
  #5  
Old 30th March 2010, 17:19
voltron81 voltron81 is offline
Senior Member
 
Join Date: Sep 2009
Posts: 292
Thanks: 1
Thanked 4 Times in 3 Posts
 
Default

Hi kamal,
at the end I've used some index and I've a better performance...

Thanks a lot and see u soon
Michele
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Step by step email setup for domain & clients aurasdoom Installation/Configuration 17 19th January 2010 12:25
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 13:58
postfix: "unknown user" with mysql auth. Kruser Server Operation 3 18th June 2009 18:20
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 09:11
Rejected e-mail (unknown user) w/Postfix doronkeller HOWTO-Related Questions 9 27th January 2008 20:09


All times are GMT +2. The time now is 17:38.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.