Joining/optimizing Nightmare

[Paula]

Hello. I have two tables NAME and BIRTH. NAME has about 10 milllion records and BIRTH has about 1 million. Each table has a field called F3, which are the first three letters of the first name, and L3, the first three letters of the last name.

I've indexed F3 in field F3 and L3 in field L3 in both tables. I've done an inner join on the fields, because I need one table with the matching records from both the NAME and BIRTH tables. Then I need to export those query results into another table.

I started the query last night at 6 p.m. This morning (10 a.m.) it is still running. I know it should take some time, but this is ridiculous. What can I do?

[peterlaursen]

two chances …

* upgrade the server (or your resources on it)

* upgrade the connection

This is a single query and it is not SQLyog that is slow. SQLyog is patiently waiting for data to arrive! But try to include a “LIMIT 1000” for instance and measure the time. Your JOIN syntax could be wrong resulting in an astronomic rows returned.

Also Check Task Manager. How much CPU is used? What is the network transfer rate?

[Paula]

The two tables are on my harddrive (400G:cool:. I'm running MySQL on my machine only. My syntax is simple:

SELECT BIRTH.*, NAME.*

FROM BIRTH INNER JOIN NAME on (BIRTH.LAST3 = NAME.LAST3) and (BIRTH.FIRST3 and NAME.FIRST3);

When I check the CPU usage, it's going back and forth from about 7 pct to 22 pct.

peterlaursen wrote on Oct 24 2006, 12:18 PM:

two chances …

* upgrade the server (or your resources on it)

* upgrade the connection

This is a single query and it is not SQLyog that is slow. SQLyog is patiently waiting for data to arrive! But try to include a “LIMIT 1000” for instance and measure the time. Your JOIN syntax could be wrong resulting in an astronomic rows returned.

Also Check Task Manager. How much CPU is used? What is the network transfer rate?

[peterlaursen]

Well – then it is not the CPU that is the 'bottleneck' her. And no connection issue either.

Did you try changing the server configuration? You find different 'templates' for the configuration file in the Server installation folder.

[Author]

Posts