CODEWITHSUNDEEP
phpmysqloptimizationindexing
Wellenbrecher
Wellenbrecher

Reputation: 179

Mysql Big Table Optimization

We have a 10-year-old website that has been using SMF. Now we wrote our very own forum script but since we are unexperienced developers, we have no idea about optimizing. Our messages table is too big (about 2 gigabytes including indexes, 2.654.193 rows total). SMF was using this table really fast but our new forum script causes high system load average.

Here is the query list: https://i.sstatic.net/V6Mpr.jpg

Here is the table structure and indexes: https://i.sstatic.net/4kDy9.jpg

Note: We use APC for acceleration and Memcached for caching. I'm a hundred percent sure that the messages table (and topics table maybe) is slowing our website.

Upvotes: 2

Views: 409

Answers (1)

Markus Winand
Markus Winand

Reputation: 8746

This is just the right moment to learn all about SQL indexing.

Proper indexing is THE way to improve SQL performance. Indexing has to be done by developers.

Consider starting here (it's the free web-edition of my book SQL Performance Explained

Major disclaimer: all links go to my own content.

Upvotes: 2

Related Questions