Solved

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

Posted on 2011-09-11
8
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 83

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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 

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
 
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 143

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 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