MySQL Forums
Forum List  »  Optimizer & Parser

Eternal SELECT queries
Posted by: Aniceto Perez
Date: November 15, 2005 03:31AM

Hi

I have two tables: A and B. Table A has one field named CODI, char(5), and 4M records. Table B has two fields named COD1, char(2), and COD2, char(3), and 100K records. The CODI field has an index, also there is an index for both COD1 and COD2.

I am testing this query:

SELECT AVG(A.pre) FROM A INNER JOIN B ON A.CODI=B.COD1+B.COD2
WHERE B.F=2;

This query generates 100K registers and takes 180 secs with MS SQL Server.

This query with mySQL on the same machine has to be aborted after 3 hours of 100% CPU without any result record.

Does anybody know how to obtain results with "large" tables and queries with joins involving 2, 3 or 4 tables?

Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Eternal SELECT queries
3080
November 15, 2005 03:31AM
2101
November 15, 2005 09:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.