Solved

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

Posted on 2011-09-11
8
207 Views
Last Modified: 2012-05-12
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
Comment
Question by:blink10
8 Comments
 
LVL 82

Expert Comment

by:Dave Baldwin
ID: 36520993
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
 

Author Comment

by:blink10
ID: 36521006
37 million
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36521070
What is the Spec of your "Server"
0
 

Author Comment

by:blink10
ID: 36521084
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 3

Expert Comment

by:woepwobin
ID: 36521124
Indexing 37 million records can easily take hours, depending on how busy mysql is and storage speed.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36521202
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
 

Author Comment

by:blink10
ID: 36526244
^ how is this done specifically?

How can i prevent mysql from coming to a standstill during this process?
0
 

Author Comment

by:blink10
ID: 36557083
For MySQL how specifically can I do this?
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now