Improving PHP MYSQL Query Performance or Server Problem?

I have this query:

SELECT
a.codigo AS code, a.nome AS name, a.especialidade AS expertise, a.telemovel1 AS mobile, a.email AS email, a.codigopostal AS zipcode
FROM table_name_a a
WHERE a.nome != ''
AND a.codigo!='5555555'
AND a.codigo!='705416'


UNION ALL

SELECT
b.cod AS code, b.nome AS name, b.especialidade AS expertise, b.telem1 AS mobile, b.mail AS email, b.cp AS zipcode
FROM table_name_b b
WHERE b.nome != ''
AND b.cod!='5555555'
AND b.cod!='705416'
AND b.cod NOT IN(SELECT assoc.codigo from table_name_a assoc) 

ORDER BY code ASC

Table A: +/-6000 rows (and growing)

Table B: +/-2000 rows (and growing)

The problem:

When the server is being used by one or two users doing several queries to the database, this query runs fine... but when the server is being used lets say during t开发者_StackOverflowhe day, with 10 users doing several queries and about +/- 1000 clients at any given hour performing some queries, the query goes from:

Showing rows 0 - 29 ( 7,138 total, Query took 0.0734 sec)

to

Showing rows 0 - 29 ( 7,138 total, Query took 100.0933 sec)

Asking:

Is this a query performance issue or the problem lies on the server configuration/performance ?

EDITED:

---

Field Types:

cod, codigo -> int(11)

nome, telemovel1, telem1, especialidade, codigopostal, cp -> varchar(200)

email -> varchar(400)

EXPLAIN QUERIES:

---

---

I offer you to rewrite this:

AND b.cod NOT IN (SELECT assoc.codigo from table_name_a assoc) 

into this:

AND NOT EXISTS (SELECT * FROM table_name_a assoc WHERE assoc.codigo = b.cod)

The rewritten query should use less memory leaving more of it for concurrent processes.

---

To try to find of how to optomise the query you should get the query plan. That will identify what indexes are used and how you can improve it. See the EXPLAIN keyword in MySql.

With that information in hand you can ensure that you have the correct indexes. Also you could consider temporary tables.