get MySQL query list of joins that do not use indexes

Question

I see this error in phpmyadmin:

The number of joins that do not use indexes

but I never use join in my code.

Now I want get a list of query that do not use indexes.

How can I get this list?

I tried enabling slow query log, but I can not understand which query is not use indexes.

Can someone guide me?

Why don't you ever use join in your code? That seems severely limiting. — Gordon Linoff, Mar 05 '21 at 15:24
That doesn't look like an error message, it looks like the name of a statistic. — Barmar, Mar 05 '21 at 16:00
hi guys i need to get list of joins that do not use indexes.please help to get this list — Mohsen Davari, Mar 05 '21 at 16:13

score 0 · Answer 1 · answered Mar 06 '21 at 01:59

There is no list of "joins not using indexes".

Certain admin queries use VIEWs that have JOINs; possibly that is where they came from.

There are valid cases in which not using an index is OK for a JOIN. A simple example is joining two tables where one of the tables has so few rows that an index would not help with performance.

The slowlog provides something more important -- a list of the "worst" queries (from a performance point of view). Any "slow" query with JOIN that needs an index will show up, even without that setting turned on. (I prefer to turn off that option, since it clutters the slowlog with unexciting queries.)

i do not have any query with join! – Mohsen Davari Mar 06 '21 at 13:55 — Mohsen Davari, Mar 06 '21 at 13:55

score 0 · Answer 2 · answered Mar 08 '21 at 15:37

I'll briefly mention that this isn't an error, it's not even really a warning. The Advisor tab is meant to make broad and generic performance suggestions that are meant to guide you towards optimizing your system. Having some of these suggestions that aren't fixable or don't apply to your situation is pretty normal. In fact, my test system gives the same advice about the join without an index.

As Rick James alluded to, these queries might not come directly from code that you write...some administrative tasks may be triggering it (your operating system might run some housekeeping queries, MySQL runs some queries against itself, etc).

The best way to look at the queries is to log them, which is answered in Log all queries in mysql. You could use the "Process" sub tab of the "Status" area in phpMyAdmin (very similar to how you get to the Advisor tab) to look at active queries, but that's not going to give you a complete picture over time. Once you have that list of queries, you can analyze them to determine if there are improvements you can make to the code.

get MySQL query list of joins that do not use indexes

2 Answers2