Adding a index while a SQL statement is running? 1

[Zych]

What would happen if I added a index into a table that was in use by another SQL statement? The statement has been running all night and I just realized that it would be faster if I indexed the main field in a table I am referencing.

Here is some background. I have a table with names and phone numbers. I have another table that has an area code field, a number field and a phone field (this field combines the area code and number into one phone number.) The phone numbers in both fields are in the same format as a char10 format. I am trying to dedupe one list from the other into a third table. The statement has worked before but these tables are on the large side. (The first is about a million and the second with the three fields is about 3.5 million.) I was thinking that if I indexed the phone field of the second table it would run faster but I don't want to start over again since I have no idea how long this will take. I am still a newbie to MySQL and SQL in general and I don't know what would happen if I added the index to the second table from a second machine.

I hope I made sense.

Thanks,

Zych

 

[hvass]

Kill the job and index the tables.

If you stick the key word EXPLAIN before your select statement and run it it will tell you how it will do the query. In general you should work out what the query will do before running a query and in most cases this means using index's.

This might help

http://www.mysql.com/doc/en/MySQL_indexes.html

 

[hvass]

Sorry forgot adding an index after the query has started will not help - the query optimiser has already determined how to do the query and cannot change its mind once it has started.

 

[Zych]

Thanks for the info. I actually did restart it and it ran much much much faster when it was indexed. Also thanks for the extra info about the EXPLAIN. It will come in handy in the future.

- Zych