Slow Query Log returning wrong row examined

Question

How the return number of rows this much, when there is only 20,000 rows i have in my database in given table. Please guide me in "row_examination". What does it mean?

 CALL DB.sp_get_program(10, 0, '', '', '', '', 0, 0, 0, '', '', @_cnet_param_p_total);
 Time: 150425  8:07:22
 User@Host: DB[User] @  [172.31.12.190]  Id: 64619
 Query_time: 62.843783  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 26264470

SELECT  (SELECT CONCAT(au.first_name,' ',au.last_name) FROM  tbl5 au WHERE admin_user_id = tbl.schedule_added_by LIMIT 1) as schedule_user_name,
(SELECT CONCAT(au.first_name,' ',au.last_name) FROM  tbl5 au WHERE admin_user_id = tbl.image_added_by LIMIT 1) as image_user_name,
(SELECT CONCAT(au.first_name,' ',au.last_name) FROM  tbl5 au WHERE admin_user_id = tbl.description_added_by LIMIT 1) as description_user_name,tbl.*
FROM
(SELECT *, 
(SELECT image FROM tbl WHERE program_id = p.program_id AND entry_type_id = 2 LIMIT 1) AS image,
(SELECT short_description FROM tbl WHERE program_id = p.program_id  AND entry_type_id = 3 LIMIT 1) AS short_description,                        
(SELECT COUNT(*) FROM tbl WHERE program_id = p.program_id 
                         AND is_live = TRUE) AS live_count
                        from tbl2 p 
                        INNER JOIN tbl3 pc on p.program_id=pc.program_id
                        INNER JOIN tbl pu on p.program_id=pu.program_id
                        INNER JOIN tbl4 c on pc.channel_id=c.channel_id         
                        WHERE pu.from_admin = FALSE AND p.is_active = TRUE
                        GROUP BY pc.program_channel_id) tbl WHERE  tbl.live_count < 3 AND (is_schedule_live= FALSE OR is_image_live=FALSE OR is_description_live=FALSE)

We can't tell - if you showed the source code for DB.sp_get_program() maybe we could take an educated guess. — symcbean, May 12 '15 at 15:22
I just wanted to know "rows_examined" consideration, Like what it includes in it, total number of rows in the main table, or it includes sub queries, joins etc all. — Amna, May 13 '15 at 03:51
It includes all of them - looks like you need to add some indexes. See the EXPLAIN plan for the query to identify where they are needed — symcbean, May 13 '15 at 11:21

score 0 · Answer 1 · answered May 12 '15 at 15:14

0

That smells like an accidental "cross join". Check your code to see if each JOIN has an ON clause.

If you need more help,

Provide source of stored program
SHOW CREATE TABLE for each table
sizes of the tables.

answered May 12 '15 at 15:14

Rick James

135,179
13
127
222

Is `admin_user_id` the `PRIMARY KEY` of `tbl5`? And other questions for which I need to see `SHOW CREATE TABLE`. – Rick James May 13 '15 at 16:34

Slow Query Log returning wrong row examined

1 Answers1