Page 1 of 1
MySQL Fine Tuning
Posted: Thu Aug 05, 2010 9:01 am
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.
Re: MySQL Fine Tuning
Posted: Thu Aug 05, 2010 10:59 am
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.
Re: MySQL Fine Tuning
Posted: Thu Aug 05, 2010 11:01 am
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.
Re: MySQL Fine Tuning
Posted: Mon Aug 09, 2010 6:17 am
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.
Re: MySQL Fine Tuning
Posted: Mon Aug 09, 2010 6:43 am
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.