CODEWITHSUNDEEP
mysqldoctrine-ormdoctrinesymfony
Gaylord.P
Gaylord.P

Reputation: 1468

My query is very Slow

With Symfony and Doctrine, I have a very simple query but very very slow

530 résults on phpmyadmin, 0,0001 seconds :

SELECT
    *
FROM
    table_a T1
    INNER JOIN
        table_b T2 ON
        T2.table_b.id = T1.id
WHERE
    T1.id = 1

And I join third table :

245390 results (530 (from table_b) X 430 (from table_c)) on phpmyadmin, 4 seconds :

SELECT
    *
FROM
    table_a T1
    INNER JOIN
        table_b T2 ON
        T2.table_b.id = T1.id
    INNER JOIN
        table_c T3 ON
        T3.table_a.id = T1.id
WHERE
    T1.id = 1

I have index A = B and A = C (With doctrine:schema:update)

I can do two queries but I do not understand the problem

Can you help me ?

Upvotes: 0

Views: 78

Answers (2)

kkarczewski
kkarczewski

Reputation: 421

It's not slow for me. But you can check use of resources with show profile/-s function. It shows you which part of your query is the slowest.

Needed documentation: http://dev.mysql.com/doc/refman/5.7/en/show-profiles.html http://dev.mysql.com/doc/refman/5.7/en/show-profile.html

Upvotes: 0

O. Jones
O. Jones

Reputation: 108706

A few things.

Four seconds to handle a result set with a quarter million rows in it isn't slow. It's actually pretty fast.

When doing JOIN operations, SELECT * is harmful. You end up with duplicate-valued columns. In a result set with a quarter million rows, that's a lot of wasted time and space, both in the MySQL server and in your client program. Instead of using SELECT *, give a list of the columns you need in the result set. Enumerating the columns you need also helps the MySQL query planner optimize things. Usually, when a query really is slow, one of the first optimization steps is to cut down the number of columns.

Notice that joining two tables causes a combinatorial explosion. That's what JOIN means. You get back every possible pair of records matching the ON clause. As you know, this is what is causing you trouble -- 530 x 430 records.

You didn't describe your data. It sounds like the rows of your table_b and table_c are separately related to table_a, but not to each other. That probably means you should retrieve table_a JOIN table_b with one query, and table_a JOIN table_c with a second query.

Upvotes: 2

Related Questions