SQL Table Indexing - Database Overloaded With Queries

Question

I'm a bit of a newbie to SQL who has been thrown in on the deep end with a server that gets about 2 million queries an hour.

I'm trying to optimise our database and I noticed this in our database runtime information:

Select_full_join 789 The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Obviously that is far from optimal. And other red flags are showing too which are probably related to this root problem I believe.

Now, we have a database structure that is not exactly well indexed. We have a unsigned INT as primary key on our tables and then just unindexed columns.

I wonder how I should approach this. Do I need to add indexes to every column? Would that even help? How should I approach this problem?

Answer 1

There are several approaches that can help.

If you just throw in more indexes, then you are likely to speed up most simple queries, as well as introduce unused indexes. Unused indexes don't really hurt if the database is read-mostly, but they take up space. Unless you care about disk space, adding single-column indexes is the fastest way to speed up table reads but also slow down table writes. Knowing the architecture, however, does help avoiding useless indexes.

You can enable logging slow queries or queries that don't use indexes:

SET GLOBAL log_queries_not_using_indexes 'on';
SET GLOBAL slow_query_log 'on';
SET GLOBAL log_output 'table';

Wait a little for the log to fill up, then query the log with

SELECT * FROM mysql.slow_log;

This should point to potential new indexes. Note that every language feature that is available for normal queries is also available here, so use what you need. Once you create a new index, you can reset and repopulate the log: TRUNCATE mysql.slow_log;

Also read up [here] for the details of slow query logging.