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?

migrated from stackoverflow.com Nov 19 '12 at 9:44

This question came from our site for professional and enthusiast programmers.

  • 2
    You do realized 789 as a running total of 2m queries/h is very small? However, do not add indexes to just anything (which would mean a performance hit on write/update), but adding them to the specific columns on which joins take place seems logical, as does adding them on columns / groups of columns frequently used in where clauses. Keep in mind that (1) a join can only use 1 index, not more, (2) a where clause can only use one index, not more, and (3) you can create indexes spanning multiple columns. But if your performance is an issue now, start with the full output of mysqlreport. – Wrikken Nov 18 '12 at 20:11
  • @Wrikken Not exactly true. dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html – tombom Nov 19 '12 at 9:44

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.

  • That sounds very interesting thank you for your solution – Amy Neville Nov 18 '12 at 23:50
  • After running the slowlog for a day, apply pt-query-digest to see what is interesting. Then post SHOW CREATE TABLE, SHOW TABLE STATUS, and EXPLAIN SELECT. We can take it from there. – Rick James Nov 20 '12 at 0:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy