Mysql query still slow even using indexes

Question

I have tried to optimaze mysql query which i use to get customers from database according address. This problem is driving me mad, so i would appreciate help :)

I have two tables related to this query: customers_address and systems_address. I want to get only customers which have address that i can show for this system.

Example:

I want get customers from customers_address table, which have address_id which are belonging to system_id 2.

Best query using in clause:

select distinct customer_id from customers_address use index(address_id_customer_id) where address_id in (select distinct address_id from systems_address where system_id = 2) and address_id !=-1\G;

Thing is that subquery only returns one row (value 2), and if i run this whole query with subquerys value it is really fast:

select customer_id from customers_address use index(address_id_customer_id) where address_id !=-1 and address_id in (2)\G;

Time drops from over 10 sec to 0.00 sec.

I have also tried to do query with joins, but it performance is still slow (over 7 seconds) when i compare to query when i have replaced value to in clause. Below same query with joins:

select distinct customer_id from customers_address use index(address_id_customer_id) inner join systems_address where systems_address.address_id = customers_address.address_id and system_id = 2 and customer_id != -1\G

I have put to customers_address 816 000 rows and systems_address 400 000 rows. Below schemas for these tables (Tables simplified that problem is easier to locate):

create table systems_address (
  `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `address_id` int(11) DEFAULT NULL,  
  `system_id` INTEGER(11)DEFAULT NULL,
   KEY `address_id` (address_id),
   KEY `system_id` (system_id))
  ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

create table customers_address (
  `id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `customer_id` int(11) DEFAULT NULL,  
  `address_id` INTEGER(11)DEFAULT NULL,
   KEY `customer_id` (customer_id),
   KEY `address_id` (address_id),
   KEY `address_id_customer_id` (address_id,customer_id),   
  FOREIGN KEY (`address_id`) REFERENCES `systems_address` (`address_id`) ON UPDATE CASCADE ON DELETE SET NULL)
  ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

Any ways to make querys faster???

Here are result of explain when i run Bohemian`s query (after creating those new indexes and updates).

id:1
select_type: SIMPLE
table: systems address
type: ref
possible_keys: address_id, system_id, address_id_system_id,idx1, key:idx1 key_len:5 ref:const rows:1999 Extra:Using where;Using Temporary

id:2
select_type:SIMPLE
table:customers_address
type:ref
possible_keys:customer_id, address_id, address_id_customer_id,idx2
key:address_id_customer_id
key_len:5
ref:database.systems_address.address_id
rows:45375
Extra: Using where;Using index

Answer 1

Reverse the order of the tables and use a join condition, which includes the extra condition:

select distinct customer_id
from systems_address
join customers_address on systems_address.address_id = customers_address.address_id
    and customer_id != -1
where system_id = 2

This should perform very well, using indexes and minimizing the number of rows accessed.

Make sure you have the following indexes defined:

create index idx1 on systems_address(system_id);
create index idx2 on customers_address(address_id);

Just to be sure, also update the statistics:

analyze systems_address, customers_address;