473,396 Members | 1,972 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Best way to Index large tables in MySQL database

Hi All,

I'm very new with MySQL database tables. I'd like to be able to index columns in large tables (40gb)size. I was wondering if youknow the best way to do so, please let me know if further information is needed. Thanks in advance!

Joe
Sep 25 '12 #1
3 1703
Rabbit
12,516 Expert Mod 8TB
I'm not sure what you mean by best way. The size of a table doesn't really dictate what and how things get indexed. It's the usage that will mostly dictate what indexes you create.
Sep 25 '12 #2
The table will be supplying data to multiple users, therefore there will be a very intense query execution. So for example, if there is a table of 50 fields, and I want to be able to create index on 10 of the columns. What would be the most efficient sql script to achieve this?
Sep 25 '12 #3
Rabbit
12,516 Expert Mod 8TB
If you want to create 10 indexes, then you just create them. There's not a whole lot of efficiency in how you tell the database to create that index.

What I mean to say is that how you tell the database to create an index doesn't have much bearing on how that index is then later used in querying the indexed field.

What's more important is the choice of indexes. Not how you tell it to create it. And the choice is determined by usage.
Sep 26 '12 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Dave Moore | last post by:
Hi All, I'm looking at using PHP and MySQL for a particular website application. However, I was a bit concerned that if a PHP script went wrong then it would be possible to accidentally delete or...
13
by: StealthBananaT | last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the...
2
by: Kevin Crosbie | last post by:
Hi all, (Sorry for the cross-post, there doesn't appear to be much activity on comp.database.oracle)I'm trying to get the last 300 rows from the difference between 2 large tables and my queries...
3
by: Jeremy Howard | last post by:
I am finding delete queries on large InnoDB tables very slow - are there ways to speed this up? I have a table with about 100 million rows: I am trying to delete just a few of these rows (the...
3
by: Sammy | last post by:
What is the best script to use to back up and admin my mysql database? Tnanks, Sammy
57
by: Bing Wu | last post by:
Hi all, I am running a database containing large datasets: frames: 20 thousand rows, coordinates: 170 million row. The database has been implemented with: IBM DB2 v8.1
3
by: Xerxes | last post by:
Hi, I would like to import the tables from MySQL database into Access database and I am not sure how to go about it. I tired to "Import External Data" from Access, selected "ODBC database" from...
5
by: mantrid | last post by:
Hello My web hosting company has lost my data due to the mysql software failing, it only affected those people using innoDB engine. I asked them why they didnt restore from a back up. They said...
6
by: fpcreator2000 | last post by:
Hello everyone. I'm having problems with a page I've created that is used to insert data into a database. It uploads two files into two distinct folder, and it takes the filenames and inserts...
0
by: tedpottel | last post by:
Hi I am trying to right a script to keep a local copy of my mysql database in a local access file. I was able to do this in Access visual basic, but cannot get it to work in python. The...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.