Need help with this SQL statement on MySQL

[Zych]

Hey Guys and Gals,

I am somewhat of a noob with MySQL. I have it running on a Linux box and usually access it via MySQL Front. I have a SQL statement I am trying to run and it seems to crash MySQL Front. (Not Responding) So then I have tried to run it on the Linux system itself. It has been running for over 24 hrs so I think it is not doing something right.

Here is the SQL statement:
SELECT * FROM Table1, Table2 WHERE Table1.Phone=Table2.Phone;

Table1 has about 800,000 records and Table2 has 400,000. I expect it to take some time but this is a little longer than I thought. On the Linux server when I hit enter I get a blinking cursor box. Is this how you run the commands on the server itself (I did log in and selected the database.)?
Should it take this long? The server has 4GB RAM and dual 2200 MP Processors.

Thanks,

- Zych

 

[swampBoogie]

What are you trying to accomplish? Are there any indexes on phone?

Potentially you can get a very large result from this query and I think that Mysql will not return anything before the query is completed.

 

[r937]

in particular, what are you planning to do with the 400,000 rows you get back? scroll through them? whoa

this is also probably a great example of where you should not use "select star" because you are asking for all columns of both tables


rudy

 

[Zych]

I have a customer who does calls all day long on a autodialer. The autodialer has a do not call list in it that is updated all of the time. What the owner wants me to do is basically remove the numbers before it is put in the autodialer.

So for now I am playing with the lists. I am not sure if I want to update a row in the future or just delete the records. When it is done all of the numbers will be exported to a list to be loaded into the dialer. I have tried the same statement but with a <> instead of the = but the client says it has run out of memory in about 20 minutes. I have not tried this statement on the server.

Thanks,

Chris

 

[JustKIDn]

zych,

Try it this way;

SELECT table1.*, table2.* FROM table1, table2 WHERE table1.Phone=table2.Phone;

I don't know if it will help, but it might.

In my (short) experience, I've noticed that MySQL seems to be case sensitive. In your example, you had 'FROM Table1, Table2' and then 'WHERE table1.Phone=Table2.Phone'

You have a mix of case in your nameing. If indeed, MySQL is case sensitive. Then I think these should be the first issues you should address.

The only thing I changed (or added) to your SQL is the 'table1.*, table2.*'. I've seen MySQL doesn't seem to like it when you use * by itself when more than one table is involved.

my .02 cents.

tgus

____________________________
Families can be together forever...

 

[Zych]

MySQL from what I understand is only case sensitive when used under Linux or any other Unix bases system. Since I am using Linux the tables must be spelled the exact way they where created in which I did. I have not thought of trying your other idea though. I'll give it a shot and see what happens.
I am also not sure if phone is indexed in the tables. If not I will set that up too.

Thanks for your reply,

Zych

 

[JustKIDn]

Zych,

You might be right about the case thing.

I've been using the windoz version so I can't say much for MySQL behaviour under Linux.

Using windoz, I haven't been able to create tables with any upper case letters. It always converts them all to lower case. On the other hand, I can create fields with upper and lower case letters.

I may be wrong, but it would seem to be inconsistant for the Linux version of MySQL to behave differently than the windoz version.

Did you get it to work for you yet?

What was the solution?


tgus

____________________________
Families can be together forever...

 

[Zych]

Actually swampBoogie came up with an answer for me. I decided to make a new table with the results and used this query:

INSERT INTO MN_032803_DD (
ID, FName, LName,
Street, City, State,
ZIP, Phone, HValue )
SELECT MN_032803.ID, MN_032803.FName, MN_032803.LName,
MN_032803.Street,MN_032803.City, MN_032803.State,
MN_032803.ZIP, MN_032803.Phone, MN_032803.HValue
FROM MN_032803 left outer join DNC_MN
ON MN_032803.Phone = DNC_MN.Phone
WHERE DNC_MN.Phone is null

The reason why MySQL acts different under Windows than Linux is because it depends on the OS's file system. Since the tables are basically directories on the server MySQL follows how the servers OS recognizes them. Windows does not care about the case but in Linux a directory named MyTable would be different from mytable. I am still a Linux noob but I do like it better for a network operating system than any of the NT NOS's at this time.

Hope this helps some other people,

- Zych