Since you're an inexperienced Elgg developer, I recommend using ready-made solutions, e.g. these plugins:
https://elgg.org/plugins/3265187
https://elgg.org/plugins/3332885
https://elgg.org/plugins/2922596
Check out the documentation for a more in-depth understanding.
1- Use PhpMyAdmin tool
2- Some old tips. Note: be careful with this if you don't understand what you're doing.
Since Elgg is using InnoDB tables you can search how to optimize MySQL for that purpose. But a lot depends on your current setup.
If your on shared hosting with the webserver and database server on the same machine, moving the database to it's own dedicated machine will help. If you're already running the database in a cluster with 10 servers than there are different thing you should look at (but than you probably don't need our help ;)
Are you experiencing any performance issues?
If you have an active community moving the search feature out of the database to a search engine will free up a lot of resources for the 'normal' use of the database. You could look into using OpenSearch as a search engine and use this plugin https://elgg.org/plugins/3333727 to connect your Elgg installation to OpenSearch.
info@elgg.org
Security issues should be reported to security@elgg.org!
©2014 the Elgg Foundation
Elgg is a registered trademark of Thematic Networks.
Cover image by Raül Utrera is used under Creative Commons license.
Icons by Flaticon and FontAwesome.