how optimize this mysql query?

Question

i have written this mysql query:

SELECT * , 1 AS haschild
       FROM table2
       WHERE parentid = '0'
             AND pid IN ( SELECT parentid FROM table2 )
UNION 
SELECT * , 0
       FROM table2
       WHERE parentid = '0'
             AND pid NOT IN ( SELECT parentid FROM table2 )
ORDER BY pid

but i think it is so silly query !! hope you can get my meaning of this query and guide me to write a better one.

thanks.

zerkms · Accepted Answer · 2010-12-14 11:41:39Z

2

   SELECT t1.*,
          IF(t2.parentid IS NULL, 0, 1) AS haschild
     FROM table2 t1
LEFT JOIN table2 t2 ON t1.pid = t2.parentid
    WHERE t1.parentid = 0
 ORDER BY t1.pid

Notes:

Create index on parentid field
If parentid is a number - don't put quotes around

answered Dec 14, 2010 at 11:41

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hd. Over a year ago

why do not put quotes around number fields?

zerkms Over a year ago

@hd: because in some cases in some reason mysql tries to cast the field to the char too (not the char to numeric) and you lose the chance to get the query be optimized by index.

LaGrandMere · Accepted Answer · 2010-12-14 11:44:21Z

0

Try :

SELECT * , CASE WHEN pid IN ( SELECT parentid FROM table2 ) THEN 1 ELSE 0 END AS haschild
       FROM table2
       WHERE parentid = '0'
ORDER BY pid

answered Dec 14, 2010 at 11:44

LaGrandMere

10.4k1 gold badge36 silver badges42 bronze badges

Collectives™ on Stack Overflow

how optimize this mysql query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related