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.
MySQL Fine Tuning
Moderator: General Moderators
Re: MySQL Fine Tuning
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.
I would have answered.
1. Ensure proper data normalization.
2. Remove duplicates.
3. Optimize indexes.
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: MySQL Fine Tuning
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.
Re: MySQL Fine Tuning
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.
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.
Re: MySQL Fine Tuning
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.