Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-11
8
Medium Priority
?
217 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 84

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

704 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