MYSQL Optimize query

Question

I am trying to optimize a query in Mysql, now this query needs 0,5 seconds and the table only have 2500 rows. I have 2 tables one table is the tickets and the other the tickets that are grouped.

Tickets:
- ID : Int (Primary Key)
- Name of the ticket: text

GroupTickets:
- ID : Int (Primary Key)
- ID_relation: Int -> the id of the group of the tickets
- ID_Ticket: Int -> the id of the ticket

My query is:

Select T.id, T.name, Count(GP.id_relation) 
FROM Tickets as T, GroupTickets as GP 
WHERE GP.id_relation IN (
    Select id_relation from GroupTickets 
    Where id_ticket=T.id
)

This select in the where clause make that mysql will do a select by every row so in the future where the table have millions of rows this query will be hard to process.

Am i wrong? Someone know a better way to take this info? I need to know if a ticket is grouped with other tickets in the query.

Best Regards.

Change IN to EXISTS or INNER JOIN, maybe create an index on id is also nesesarry. — Blank
– Blank, Commented Jul 10, 2017 at 8:20

etsa · Accepted Answer · 2017-07-10 12:18:14Z

1

Try this (Your query has a redundant IN SELECT ...)

Select T.id, T.name, Count(GP.id) AS RC
FROM Tickets as T
INNER JOIN GroupTickets as GP  ON GP.ID_TICKET = T.ID
GROUP BY T.ID, T.name

New version for new question as in comment below: May be you want something like this:

SELECT A.ID, A.NAME, B.ID_RELATION, C.RC
FROM TICKETS A
INNER JOIN GROUPTICKETS B ON B.ID_TICKET = A.ID
INNER JOIN (SELECT ID_RELATION, COUNT(*) AS RC
            FROM GROUPTICKETS A
            GROUP BY ID_RELATION) C ON C.ID_RELATION = B.ID_RELATION
;

Could also be useful

CREATE INDEX GROUPTICKETS_IX01 ON GROUPTICKETS(ID_RELATION, ID_TICKET);

edited Jul 10, 2017 at 12:18

answered Jul 10, 2017 at 8:20

etsa

5,0501 gold badge9 silver badges18 bronze badges

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

3 Comments

Strawberry Over a year ago

@Roke And make sure ID_Ticket is indexed

Roke Over a year ago

Sorry i made a mistake in my query, what i need is to get all rows with the same id_relation. WIth your query always get 1 in the id_relation field.

etsa Over a year ago

I'll try to understand your new question (could you post some sample data and expected results?)

Collectives™ on Stack Overflow

MYSQL Optimize query

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