Help: Optimize this query in MySQL?

I'd suggest giving the following a try; the subquery might use the key better than the join in this context. Take care, though; I couldn't swear on a stack of K & R's that the query is the same as your original SELECT *, (SELECT * FROM tbl_clienteEnderecos WHERE int_clienteEnderecos_cliente_id_fk = int_clientes_id_pk AND str_clienteEnderecos_uf = "SP") AS T2 FROM tbl_clientes GROUP BY str_clientes_nome_original, int_clientes_id_pk HAVING T2. Int_clienteEnderecos_id_pk IS NOT NULL ORDER BY str_clientes_nome_original, int_clientes_id_pk LIMIT 0, 20.

I'd suggest giving the following a try; the subquery might use the key better than the join in this context. Take care, though; I couldn't swear on a stack of K & R's that the query is the same as your original. SELECT *, (SELECT * FROM tbl_clienteEnderecos WHERE int_clienteEnderecos_cliente_id_fk = int_clientes_id_pk AND str_clienteEnderecos_uf = "SP") AS T2 FROM tbl_clientes GROUP BY str_clientes_nome_original, int_clientes_id_pk HAVING T2.

Int_clienteEnderecos_id_pk IS NOT NULL ORDER BY str_clientes_nome_original, int_clientes_id_pk LIMIT 0, 20.

1 this query doesn't work: Query : SELECT *, (SELECT * FROM tbl_clienteEnderecos WHERE int_clienteEnderecos_cliente_id_fk = int_c... Error Code : 1241 Operand should contain 1 column(s). Thank you. – Daniel Koch Jul 20 '10 at 12:45 Hmmm.

Yes, you're right. I'll have a think about it. – Brian Hooper Jul 20 '10 at 13:17 I've thought about it, and been unable to come up with any modification to this query that doesn't turn it into what you were trying anyway.

I'm sorry to have taken up your time. – Brian Hooper Jul 20 '10 at 19:11 No problem, thanks a lot anyway. – Daniel Koch Jul 20 '10 at 20:25.

MySQL will typically not use an index if it will not help narrow the results down enough. It appears that "SP" occurs in roughly 670654 rows. Since this is about 1/3 of your total rows, it is more efficient to read it in disk order.

You can try an index to tbl_clienteEnderecos: KEY `test` (`str_clienteEnderecos_uf `, `int_clienteEnderecos_cliente_id_fk`) This might be enough to get it to use the index. What is the difference between these two columns? They look like they should be the same.

Int_clienteEnderecos_id_pk int_clienteEnderecos_cliente_id_fk ------ edit below ----- I understand what the names of the columns imply. I was just curious if the two values should be identical. If they are, it would simplify a few things and have them be joined on the primary key of the tables.

I am not sure about the specific meaning of the tables involved, so I don't know if there is a 1-1 or 1-0 relationship between them or a one to many relationship. I suggest trying to retrieve just the primary key of the tables that you want. For instance, instead of select * try: EXPLAIN SELECT int_clienteEnerecos_id_pk, int_clientes_id_pk FROM tbl_clientes LEFT JOIN tbl_clienteEnderecos ON int_clienteEnderecos_cliente_id_fk = int_clientes_id_pk WHERE str_clienteEnderecos_uf = "SP" GROUP BY str_clientes_nome_original, int_clientes_id_pk ORDER BY str_clientes_nome_original, int_clientes_id_pk LIMIT 0,20 If this works out the way I hope it will, you sell see "from index" in the Extra column.

If you need additional fields returned, you can either make another round trip to fetch them, or add them to your index. Or use a nested query to fetch them based on the results of the query above. Also, why are you grouping by and ordering by the same thing?

Are you expecting multiple matches of the foreign key? Cheers, Jacob.

Int_clienteEnderecos_id_pk is PRIMARY KEY of tbl_clienteEnderecos table. Int_clienteEnderecos_cliente_id_fk is FOREING KEY of tbl_clientes table on tbl_clienteEnderecos table. – Daniel Koch Jul 20 '10 at 6:13 I've tried your suggestion, to use this KEY (str_clienteEnderecos_uf , int_clienteEnderecos_cliente_id_fk).

Not working, same result: Using where; Using temporary; Using filesort. – Daniel Koch Jul 20 '10 at 6:18 The short answer is that your filter is basically useless. You don't filter out enough rows for the query to use the indexes.

Adding another suggestion to my answer above. – TheJacobTaylor Jul 20 '10 at 20:33 I'm getting the same slow result. Tbl_clientes can be translated to "customers" table, while tbl_clienteEnderecos can be trabslated as "customer_addresses".

Int_clienteEnderecos_id_pk is id field, while int_clienteEnderecos_cliente_id_fk is the foreing key: customer_id. – Daniel Koch Jul 21 '10 at 20:18 Sorry to hear that. In terms of the naming, here is where I was headed with the duplicate column question.

If each customer_addresses row has exactly one customer_id, why don't you just use the customer_id as the primary key for the address table? If there can be more than one customer_id associated with an address then you should put the customer_addresses key as a foreign key in the customers table. Not sure how to help on optimizing the query.

– TheJacobTaylor Jul 21 '10 at 21:41.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.