How to create an index on a large dataset....

I am trying to index a column of 300 varchar in mysql. However, it just freezes up my server everytime i attempt this or doesnt do it.

How can i create an index with letting it crash?
blink10Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you should not do it with the GUI as such. create the T-SQL for it, and run it on the server using the sqlcmd command line, for example, to avoid timeouts.
but still, it shall take time to index the 37M rows
0
 
Dave BaldwinFixer of ProblemsCommented:
This page http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html says "The maximum key length is 1000 bytes." so '300 varchar' shouldn't be too large.  How many rows do you have in the table?
0
 
blink10Author Commented:
37 million
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Neil RussellTechnical Development LeadCommented:
What is the Spec of your "Server"
0
 
blink10Author Commented:
Its a Intel Quad Core Xeon X3440 (4 x 2.53GHz, 8MB Cache, Hyper Threaded)

I just deleted all the records for an attempt at a new approach. Since the goal of the index was to using for a match verification process, i decided to md5 all those from varchar 300 to varchar 32, ill let you know if this helps at all.

However, is there a best way to add an index? I tried in php my admin, i tried in a php file, both seemed to time out and kill the server. Thoughts on how I can index these 37 million rows efficiently?  
0
 
woepwobinCommented:
Indexing 37 million records can easily take hours, depending on how busy mysql is and storage speed.
0
 
blink10Author Commented:
^ how is this done specifically?

How can i prevent mysql from coming to a standstill during this process?
0
 
blink10Author Commented:
For MySQL how specifically can I do this?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.