MySQL Optimizing Query With IN

Question

SELECT `listener` ,
SEC_TO_TIME( SUM( TIME_TO_SEC( `call_time` ) ) ) AS total_time,
COUNT( `listener` ) AS number
FROM calls
WHERE listened_date = '2013-05-09'
AND type in ('column1','column2')
AND id
IN ( SELECT id
FROM calls
GROUP BY CONCAT( name, ' ', when ) )
GROUP BY `listener`

This query is working so slow and making other queries not working in same time. How can i make this lighter?

I think IN make it slower. What is alternative in this case?

just the opposite - creating an index on a column will increase querying speed greatly, although it will grow the memory size of the db a bit. You can create an index on your table with "CREATE INDEX id_index ON calls (id) USING BTREE;". Note that 1. this should be an addition to optimizing your mysql query (other answers) 2. you should reindex the table after adding new records ("OPTIMIZE TABLE calls"), i.e. it is not a good solution if you have frequent inserts/deletions on the table — ılǝ
– ılǝ, Commented May 9, 2013 at 8:03
@ile Average 9 insert, update, delete query per second working in table. — milesh
– milesh, Commented May 9, 2013 at 8:14
there is an average of 9 inserts, delete, updates on the table? :| are you sure, seems highly exaggerated?... How many records do you have? — ılǝ
– ılǝ, Commented May 9, 2013 at 8:18

Justin · Accepted Answer · 2013-05-10 19:30:07Z

2

Maybe ?:

SELECT c.`listener` ,
       SEC_TO_TIME(SUM(TIME_TO_SEC(c.`call_time`))) AS total_time,
       COUNT(c.`listener`) AS number
FROM calls c
WHERE c.listened_date = '2013-05-09'
  AND c.TYPE IN ('column1',
                 'column2')
  AND EXISTS (SELECT 0
              FROM calls c2
              WHERE c2.id = c.id)
GROUP BY c.`listener`

edited May 10, 2013 at 19:30

answered May 9, 2013 at 7:55

Justin

9,7336 gold badges38 silver badges49 bronze badges

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

3 Comments

milesh Over a year ago

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT 0 FROM calls c2 WHERE c2.id = c.id) GROUP BY c.listener LIM' at line 1

Justin Over a year ago

I don't think you can do more than this... maybe just summing call time can be like this - SEC_TO_TIME(SUM(c.call_time))

milesh Over a year ago

About your edit; i was using that for CONCAT; it eliminates records with same listener and same time. This query doesn't do same thing.

Collectives™ on Stack Overflow

MySQL Optimizing Query With IN

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related