CODEWITHSUNDEEP
phpmysqloptimizationquery-optimization
Christian
Christian

Reputation: 96

MySQL Table Optimization

I'm looking to optimize a few tables in a database because currently under high load the wait times are far too long...

Ignore the naming schema (it's terrible), but here's an example of one of the mailing list tables with around 1,000,000 records in it. At the moment I don't think I can really normalize it anymore without completely re-doing it all.

enter image description here

Now... How much impact will the following have:

One other thing I'm interested in, a couple of tables including this one use InnoDB as opposed to the standard MyISAM, is this recommended?

The front-end is coded in PHP so I'll be looking through that code aswell, at the moment I'm just looking at a DB level but any suggestions or help will be more than welcomed!

Thanks in advance!

Upvotes: 0

Views: 1180

Answers (1)

spencer7593
spencer7593

Reputation: 108380

None of the changes you propose for the table are likely to have any measurable impact on performance.

Reducing the max length of the VARCHAR columns won't matter if the row format is dynamic, and given the number and length of the VARCHAR columns, dynamic row format would be most appropriate.)

What you really need to tune is the SQL that runs against the table.

Likely, adding, replacing and/or removing indexes is going to be the low hanging fruit.

Without the actual SQL, no one can make any reliable tuning recommendations.

For this query:

SELECT email from table WHERE mailinglistID = X.

I'd make sure I had an index on (mailinglistId, email) e.g.

 CREATE INDEX mytable_ix2 ON mytable (mailinglistId, email);

However, beware of adding indexes that aren't needed, because maintenance of indexes isn't free, indexes use resources (memory and i/o).

That's about the only tuning you're going to be able to do on that table, without some coding changes.

To really tune the database, you need to identify the performance bottleneck. (Is it the design of the application SQL: obtaining table locks? concurrent inserts from multiple sessions blocking?, or does the instance need to be tuned: increase size of buffer cache, innodb buffer cache, keysize area, SHOW INNODB STATUS may give you some clues,

Upvotes: 2

Related Questions