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
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/en/using-explain.html Cheers -- Kamal
Hi Kamal, thanks for your reply This is the output: [/url][/IMG] Next time that you're in London I've to pay you a beer... Cheers Michele
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
Hi kamal, at the end I've used some index and I've a better performance... Thanks a lot and see u soon Michele
