0

For the below queries I already have a spatial index and indexes on the primary key (ID). Other than that I would like to know how I should set my indexes to make the below queries fast. In some cases the queries are very slow (more than 3 seconds). I'm only using SELECT statements on the tables which need to be fast, so it is not a problem in INSERT is taking longer.

I have two different queries in use.

QUERY 1:

SELECT *

FROM B

LEFT JOIN A AS C
ON B.id = C.id
LEFT JOIN A AS D
ON B.arid = D.id
LEFT JOIN E       
ON B.opid = E.id

WHERE C.type='aa' AND D.type='aa'
AND
MBRWithin(C.lat_lng_point, GeomFromText('Polygon((57.5708387771 -6.08774442528, 54.3332809958 -6.08774442528, 54.3332809958 -0.305217949274, 57.5708387771 -0.305217949274, 57.5708387771 -6.08774442528))'))
AND
MBRWithin(destinationsto.lat_lng_point, GeomFromText('Polygon((50.4721888907 -0.111395950546, 47.2346311093 -0.111395950546, 47.2346311093 4.80899595055, 50.4721888907 4.80899595055, 50.4721888907 -0.111395950546))'))
LIMIT 0, 50

QUERY 2:

SELECT *
FROM (
SELECT (GLength(
LineStringFromWKB(
  LineString(
    lat_lng_point,
    GeomFromText('POINT(55.9520598864937 -3.19648118727903)')
  )
 )
)) 
AS distance 

FROM (
SELECT * FROM A 
WHERE MBRWithin(lat_lng_point, GeomFromText('Polygon((56.2218563683 -3.67835839361, 55.6822634047 -3.67835839361, 55.6822634047 -2.71460398094, 56.2218563683 -2.71460398094, 56.2218563683 -3.67835839361))'))
AND A.type = 'rr'
) AS A

LEFT JOIN B           
ON A.id = B.id
ORDER BY distance ASC, main DESC
) AS t

GROUP BY trid
ORDER BY distance ASC
LIMIT 30

My question is: How do I set my table indexes for the queries above?

Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
BastiaanWW
  • 1,259
  • 4
  • 18
  • 34
  • 1
    It's difficult to give a precise answer without knowing the sizes of the various tables. The general answer is that you should have indexes on columns that are compared directly in ON and WHERE clauses. Use `EXPLAIN` to see which index are being used by a query. – Barmar Nov 20 '13 at 06:00
  • @Barmar Table A has many rows. Should I use a single index for each compared column or an index with multiple columns? – BastiaanWW Nov 20 '13 at 06:10
  • Use composite indexes if you're comparing multiple columns in two tables. Use separate indexes if you're comparing each column with columns in different tables. Since you're comparing each column in B with columns in different tables, they should be separate indexes. – Barmar Nov 20 '13 at 06:12
  • @Barmar What about the distance which is a derived column based on input to the query (based on Polygon) that doesn't have an index and will become slow. – BastiaanWW Nov 20 '13 at 06:46
  • 1
    You can't index a calculation, only data that exists in the table. – Barmar Nov 20 '13 at 06:51

0 Answers0