MySQL Fine Tuning

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

MySQL Fine Tuning

Post by devarishi »

The problem statement reads:

There are tens of tables and thuosands records in a MySQL Database. How will you Fine Tune MySQL for Better Performance?

That was asked in the interview I attended today.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: MySQL Fine Tuning

Post by JakeJ »

And they wanted you to answer that without any clue as to the data they contain?

I would have answered.

1. Ensure proper data normalization.
2. Remove duplicates.
3. Optimize indexes.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: MySQL Fine Tuning

Post by AbraCadaver »

Yes, and beyond that, with only tens of tables and thousands of records, I wouldn't spend any time on any more optimizations unless there was a performance problem.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
devarishi
Forum Contributor
Posts: 101
Joined: Fri Feb 05, 2010 7:15 pm

Re: MySQL Fine Tuning

Post by devarishi »

Hi,


I thought he was expecting some commands for "Tuning" the database.

I want to add one thing here. I used "AUTO INCREMENT" for an ID field. Let's say there are 10 records with ID starting from 1 to 10. Now we remove last 2 records. So, the lasr ID is numbered 8. We add a new record. But the ID is numbered 11.

How can we correct all the IDs or Serial_Numbers? Okay, at this point I can think of UPDATE the field. Any other solution?

By the way, thanks for your inputs.
JakeJ wrote:And they wanted you to answer that without any clue as to the data they contain?

I would have answered.

1. Ensure proper data normalization.
2. Remove duplicates.
3. Optimize indexes.
JakeJ
Forum Regular
Posts: 675
Joined: Thu Dec 10, 2009 6:27 pm

Re: MySQL Fine Tuning

Post by JakeJ »

Using your example of deleting 2 records and the next record starting at 11 isn't really a performance issue. Generally speaking, you do NOT want to reindex (which would mean the next record has an id of 9) because you might have fields in other tables referring to that and you will not get correct results. You'll get indexes pointing to the wrong places.
Post Reply