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

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?
0
blink10
Asked:
blink10
1 Solution
 
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
 
Neil RussellTechnical Development LeadCommented:
What is the Spec of your "Server"
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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
 
Guy Hengel [angelIII / a3]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
 
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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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