Solved

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

Posted on 2011-09-11
8
212 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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 video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

734 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