CODEWITHSUNDEEP
mysqlsqldatabase-designindexingquery-optimization
ry b
ry b

Reputation: 87

Query Optimizing with 3 tables

Looking to optimize this query

SELECT gwt.z, gwt.csp, gwt.status, gwt.cd, gwt.disp, gwt.5d, gwt.6d, gwt.si, gwt.siad, gwt.prbd, 
  CONCAT(gwt.1, gwt.2, gwt.3, gwt.4, gwt.5, gwt.6, gwt.7, gwt.8, gwt.9), 
  group_concat(gws.res order by line_no), gwt.scm, gm.me, gwt.p, gwt.scd 
from gwt 
left outer join gws on gwt.csp = gws.csp 
left join gm on gwt.scm = gm.mid 
where gwt.zone = 1 
  and (status like '1%' or status like '2%' or status like '3%' or 
       status like '4%' or status like '5%' or status like '6%') 
group by gwt.csp

Using EXPLAIN, gwt has 4110 rows, gws has 920k rows, and gm has 2800 rows.

The query loaded fine when I was only querying status like 1%, but since I've added additional statuses to display, I get a timeout error.

describe

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I would suggest the following.

Be sure that each table has an index on what looks like its primary key:

  • gwt.csp
  • gm.mid

For gwt, create another index on (zone, status) and change the join condition to:

gwt.zone = 1 and status >= '1' and status < '7'

This is equivalent to your list, but it will allow the execution engine to use an index.

That might be enough to fix the query. Finally, you can put an index on gws.csp, to see if that speeds things up.

Is "csp" a one-to-one relationship? You might have a problem with the query creating a giant result set, if it is not.

Upvotes: 2

McGarnagle
McGarnagle

Reputation: 102793

Since the gws table has two orders of magnitude more rows than the other tables, this is the one to focus on. If you want to design your index to target this particular query, then the first step is straightforward. Namely, you'll want to add an index on the joined column (gws.csp) and make sure to include all selected columns -- gws.res and gws.line_no(?) -- in the index.

The above should improve the speed of the query dramatically. A secondary concern would be to make sure that the gwt table has an index with status as the first column.

Upvotes: 1

Related Questions