• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 441
  • Last Modified:

MySQL speed up query with index?

I am comparing 2 tables each with common field called mniNum and removing duplicate records from the one table by comparing records in each table.   If I create an index in each table using the common field will that help speed up the process?

What else can I do to speed up a query that must compare each record between two tables?
0
pda4me
Asked:
pda4me
  • 2
1 Solution
 
K VDatabase ConsultantCommented:
surely it will. use Explain <QUERY> to find out index hints.
If you can post your queries and table definition it'd be clear picture.
0
 
Leo TorresSQL DeveloperCommented:

Well sounds like you have answered you own question if this column your comparing is not a primary key then write an index for that column..

FYI.. Primary Keys automaically get an index Clustered Indexs

You you have to create an index on a column that is a non-clustered Index

These indexs will imporve look times on tables

By imporoving Lookup when you add Indexs on a table you will increase insert times so I hope you dont have a lot of inserts to this table or large inserts for that fact ..

You have a trade of there you going to have to deal with and decide which is more important..

good luck!!
0
 
pda4meAuthor Commented:
Ltorres,  will delete also be increased in addition to insert times when there is an index?  I am actually comparing the tables and removing records.
0
 
Leo TorresSQL DeveloperCommented:
Yes corect deletes as well

Here is a good resource
http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/data_acc.htm

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now