0

I have this query in MySQL:

SELECT 
    p.idSentence, 
    p.idPath, 
    p.token, 
    p.posTag, 
    p.ner, 
    p.isTV, 
    r.relationType, 
    t.tag     
FROM path p 
 LEFT OUTER JOIN relation r ON(p.idTokenN=r.idTokenN) 
 JOIN contentDoc t ON p.idContDoc=t.idContDoc
 JOIN (
    SELECT idPath, 
           MAX(isTV='true') AS hasTV,
           COUNT(*) AS pathLength
    FROM path
    GROUP BY idPath 

  ) pf 
 ON p.idPath=pf.idPath 
    AND pf.hasTV =1 
    AND pf.pathLength BETWEEN 2 AND 5
 JOIN (
     SELECT idSentence,COUNT(DISTINCT(idPath)) AS paths
     FROM path
     GROUP BY idSentence
    ) ps
 ON p.idSentence=ps.idSentence
    AND ps.paths BETWEEN 1 AND 3 ;

Because the data are so many, the query takes a long time to run, how can I optimize it to speed it up?

  • 1
    Which are your indexed columns in these tables? – Radu Gheorghiu Apr 8 '14 at 15:50
  • 3
    If you want us to help optimize a query, you need to show us the table and index definitions, as well as row counts for each of the tables. Maybe your tables are defined poorly. Maybe the indexes aren't created correctly. Maybe you don't have an index on that column you thought you did. Without seeing the table and index definitions, we can't tell. We also need row counts because that can affect query optimization greatly. If you know how to do an EXPLAIN or get an execution plan, put the results in the question as well. If you have no indexes, visit use-the-index-luke.com ASAP. – Andy Lester Apr 8 '14 at 16:37
0

Here is some recommentations:

Make sure all the columns used in the joins are indexed properly.

if possible, replace subqueries with indexed temporary tables.

Move when possible the filtering clause from the join statement to the where statement (e.g. Where pf.pathLength BETWEEN 2 AND 5)

Try to move the joins order to start filtering with the table which reduce the more efficiently the returned dataset.

| improve this answer | |
  • you give an example for this point: Move when possible the filtering clause from the join statement to the where statement (e.g. Where pf.pathLength BETWEEN 2 AND 5) ? – user3438154 Apr 9 '14 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.