How to interpret explain sql?

Question

SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.293413 * SQRT( pow( - 6.185 - TB.Latitude, 2 ) + pow( 106.773 - TB.Longitude, 2 ) * 0.988392289802 ) AS Distance
FROM `tablebusiness` AS TB
JOIN tableauxiliary AS TA ON TA.BusinessID = TB.ID
WHERE MBRContains( GeomFromText( 'MULTIPOINT(-6.23178308133 106.726216919,-6.13821691867 106.819783081)' ) , TA.Latlong )
AND MATCH (
TA.FullTextSearch
)
AGAINST (
'res*'
IN BOOLEAN
MODE
)
AND TA.Prominent >15
ORDER BY Distance

This query takes 12 seconds.

If I do explain

I saw this easy explanation

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  TA  fulltext    PRIMARY,Prominent,LatLong,LatLong_2,FullTextSearch  FullTextSearch  0       1   Using where; Using temporary; Using filesort
1   SIMPLE  TB  eq_ref  PRIMARY     PRIMARY     752     gensurv_isikota.TA.BusinessID   1   

All has 1 row. I wonder what the hell does that mean?

Answer 1

Explaining the significant parts of that EXPLAIN:

1. Go to table TA and use FULLTEXT search. I would expect this to be first since MATCH..AGAINST takes priority over other clauses in WHERE. It estimated that "1" row would be found. That is an estimate, regardless of the Engine. (There are a few cases where it will be exact. Mahesh's comment refers to SHOW TABLE STATUS.)

2. Reach into table TB via the PRIMARY KEY. The PK consumes up to 752 bytes. (Perhaps VARCHAR(250) CHARACTER SET utf8?). It is using gensurv_isikota.TA.BusinessID for the value. It expects to get 1 row. (It can't find more than 1, since a PK is UNIQUE.)

Missing from the EXPLAIN is any useful hints about the rest of the WHERE (MBRContains and Prominent>15) other than to say "Using where".

Also missing is anything about the ORDER BY.

"Using temporary; Using filesort" is unnecessarily scary. It means that it had to create a temporary table and sort it. But it does not necessarily mean that it touched the disk. It will first try to do the tmp table as a MEMORY table. If that fails, it will use MyISAM, and probably hit the disk. If it truly was only 1 row (or even 1000), then this step will be so fast as to be inconsequential.

But your real question is "why 12 seconds"?

First, find out how many rows MATCH 'res*'. The speed of that query depends primarily on how many rows.

Next, provide SHOW TABLE STATUS and SHOW CREATE TABLE for each table. Also, provide SHOW VARIABLES LIKE '%buffer%'. I suspect you have default cache sizes -- see http://mysql.rjweb.org/doc.php/memory .

Another thing to note. The query must gather all possible rows before it can start the ORDER BY. Even if you added a LIMIT.

How to speed it up?

IF you are pretty sure that the MBRContains will filter down to fewer rows than the MATCH, then use a subquery to find ids from the MBR, then JOIN to the rest. This might be faster.

Another thought... Two subqueries of the form

    SELECT ... 
    FROM ( SELECT id... MBRContains ...) s
    JOIN ( SELECT id ... MATCH ... ) f  ON f.id = s.id
    JOIN other tables, etc...

But that gets messy and iffy.