Select_full_join number that keep increasing

Question

I'm stuck for a while now, with a Select_full_join number that keep increasing.

I'm using "log-queries-not-using-indexes", I looked at the mysql-slow.log and I found a lot of querys like this one :

# Time: 131106 16:44:51
# User@Host: XXX @ localhost []
# Query_time: 0.000497  Lock_time: 0.000061 Rows_sent: 1  Rows_examined: 0
SET timestamp=1383752691;
SELECT COUNT(*) AS expression
FROM 
(SELECT 1 AS expression
FROM 
com_c_jobs_listing cj
WHERE  (category IN  ('Operations', 'Business Development', 'Sales/Account Management', 'R&D', 'Internal IT')) AND (country IN  ('Brazil', 'China', 'France', 'Germany', 'Italy', 'Japan', 'Korea', 'Netherlands', 'Russia', 'United Kingdom', 'United States')) ) subquery;

I don't get why this is logged. Also, there is an index on both category and country.

I think I missed something but I can't find what...

This is the result of the explain query :

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 2 DERIVED cj range category,country category 302 NULL 86 Using where

What should I do? Any idea?

Yes, I updated the description. It's using "where" and I think it's ok, but I may be wrong. — Deu, Nov 06 '13 at 17:50
Your outer query has no INDEX as it is on the table created from the derived subquery. — ModulusJoe, Nov 06 '13 at 18:48

score 0 · Answer 1 · answered Nov 06 '13 at 18:54

From your explain extended (truncated here):

id  select_type possible_keys       key         key_len
1   PRIMARY     NULL                NULL        NULL 
2   DERIVED     category,country    category    302

You can see that a key is being selected for the derived subquery, but your parent query is counting on a derived table, so not indexed (no possible keys).

Why did you have a subquery and just not count the rows directly?

SELECT COUNT(*) AS expression
FROM com_c_jobs_listing cj
WHERE  category IN  ('Operations', 
  'Business Development', 'Sales/Account 
  Management', 'R&D', 'Internal IT')
AND country IN  ('Brazil', 'China', 'France', 
  'Germany', 'Italy', 'Japan', 'Korea', 'Netherlands',
  'Russia', 'United Kingdom', 'United States');

score 0 · Answer 2 · answered Jan 24 '23 at 23:40

The log-queries-not-using-indexes setting does just that. It logs queries that aren't using indexes. However, in order for a query to count towards select_full_join, it must be a join that causes a full table scan.

While the documentation doesn't explain how to track these queries down, you can query the performance_schema.events_statements_history table (and it's "current" and "history_long" siblings) to find the actual statements. The table includes the columns select_full_join and select_full_range_join which will be non-zero if the query meets those criteria. The sql_text column will contain the SQL itself and current_schema will tell you the database it was run in.

It should be noted that the performance_schema event tables don't track every query from everywhere, so you may need to query the table regularly until you find your culprit. See the MySQL documentation for further details.

Select_full_join number that keep increasing

2 Answers2