473,326 Members | 2,090 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,326 software developers and data experts.

MySQL Performance (DB with many small tables or DB with fewer large tables)

2
Hi,
I have a database and I am expecting a lot of data to come in. I was thinking to split up the database to multiple tables i.e. if the surname of a person starts with aa to ab i put it in table a0, if ac to ad i put it in table a1. Will this make my querying faster or is it better to put it in a large table? Or if ever are there any other ways to make my database more efficient. I am also expecting high traffic in my web site so can you suggest some techniques to ease the load on the database server?
May 18 '07 #1
3 2202
pbmods
5,821 Expert 4TB
Use indexes. With indexes, it doesn't matter how big your table is; if your query data can be found in an index, MySQL will know exactly where to search.

If you have good indexes, you might have 100,000 rows, but MySQL still might only have to search a dozen of them at most (depends on the query and how many results you expect to get back, but for example, if you search for a primary key, MySQL only has to search ONE row, regardless of the total number of rows!).

Here's some info on indexes:
http://www.databasejournal.com/featu...le.php/1382791

If you want to test your indexes, use the EXPLAIN keyword on any query:
http://dev.mysql.com/doc/refman/5.0/en/explain.html

Also, MyISAM tables are generally faster than InnoDB tables for large datasets.
May 19 '07 #2
jsia18
2
Use indexes. With indexes, it doesn't matter how big your table is; if your query data can be found in an index, MySQL will know exactly where to search.

If you have good indexes, you might have 100,000 rows, but MySQL still might only have to search a dozen of them at most (depends on the query and how many results you expect to get back, but for example, if you search for a primary key, MySQL only has to search ONE row, regardless of the total number of rows!).

Here's some info on indexes:
http://www.databasejournal.com/featu...le.php/1382791

If you want to test your indexes, use the EXPLAIN keyword on any query:
http://dev.mysql.com/doc/refman/5.0/en/explain.html

Also, MyISAM tables are generally faster than InnoDB tables for large datasets.
Is this true even if a table contains 1 million records? If i split the tables up like what i've said in my first post what are its disadvantages? By the way one person will have multiple entries and i need to query all of the records of each person but ill do it 10 records at a time so i need to repeatedly query the table
May 19 '07 #3
Motoma
3,237 Expert 2GB
Is this true even if a table contains 1 million records? If i split the tables up like what i've said in my first post what are its disadvantages? By the way one person will have multiple entries and i need to query all of the records of each person but ill do it 10 records at a time so i need to repeatedly query the table
The disadvantages is the fact that you will need to perform multiple queries and a UNION in order to return one dataset.

Indexes are the way to go in this situation, as they were designed with this purpose in mind.
May 21 '07 #4

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

Similar topics

3
by: es22 | last post by:
Hi, I'm trying to decide whether to use one large table or many small tables. I need to gather information from various devices (about 500). Each device has its own Id and some data. Should I...
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...
4
by: Dag Sunde | last post by:
Just wondering if anyone have looked into this? How to split up ones JavaScript library? A lot of very specific (and small) .js files, or a few larger files. I'm thinking about load-time...
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
1
by: Dino Vliet | last post by:
Hi folks, I'm busy with analyzing some data and therefore will have to store 2 big tables containing 50 million obs & 25 million obs. Selecting some interesting obs can decrease these tables with...
1
by: murray | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment...
0
by: OctoTools | last post by:
Large table performance problem with MYSQL performance with ASP.NET I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results. Environment...
0
by: Bucker | last post by:
Could someone view the following that I copied from phpMyAdmin and tell me from the statistics if are server is running OK? Does it look like we will have problems as more people hit our server?...
4
by: =?Utf-8?B?VzFsZDBuZTc0?= | last post by:
When one architects a new project one of the first steps in the decision is to decide on the layers. (In my opinion anyway) One architecture that I have used before is to go solid OO and create...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.