Slow query, unknown why

Hi,

I'm still stuck with my SQL query that is slow but really shouldn't be.

The problem is that I cannot create a simple test case. I could only
provide you a whole lot of pages of PHP code and SQL queries to explain
the problem.

I have now three versions of my query. One with a sub select, which
takes 40 ms and works. One with a left join instead, which takes 40 ms
and works. And one with an inner join instead, which takes 3 ms and
doesn't work. The number of left-joined rows should be around 5, so what
can make it take 35 ms to join those handful of rows?

MySQL server version is 5.0.67 and 5.1.41 (just updated).

Here's a small impression of my query:

SELECT t.TagId, t.TagName, tk.UserId
FROM message_revision_tag mrt
JOIN tag t USING (TagId)
LEFT JOIN keylist tk ON -- Here's the left join
(tk.KeylistId = t.ReadAccessKeylistId AND
tk.UserId IN (22943, 10899))
WHERE mrt.MessageId = 72 AND
mrt.RevisionNumber = 1 AND
t.ReadAccessKeylistId IS NOT NULL;

This is only a sub-query of a larger search query in my PHP application.
MySQL workbench can't show query timings so I can'T say how long this
part of the query takes. It's probably fast, but it is applied to ~600
other rows to determine whether they should be included in the results
or not.

--
Yves Goergen "LonelyPixel"
Visit my web laboratory at http://beta.unclassified.de

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Slow query, unknown why

On Sun, Apr 25, 2010 at 9:12 AM, Yves Goergen
wrote:
> Hi,
>
> I'm still stuck with my SQL query that is slow but really shouldn't be.
>
> The problem is that I cannot create a simple test case. I could only
> provide you a whole lot of pages of PHP code and SQL queries to explain
> the problem.
>
> I have now three versions of my query. One with a sub select, which
> takes 40 ms and works. One with a left join instead, which takes 40 ms
> and works. And one with an inner join instead, which takes 3 ms and
> doesn't work. The number of left-joined rows should be around 5, so what
> can make it take 35 ms to join those handful of rows?
>
> MySQL server version is 5.0.67 and 5.1.41 (just updated).
>
> Here's a small impression of my query:
>
> SELECT t.TagId, t.TagName, tk.UserId
> FROM message_revision_tag mrt
> =A0JOIN tag t USING (TagId)
> =A0LEFT JOIN keylist tk ON =A0 -- Here's the left join
> =A0 =A0(tk.KeylistId =3D t.ReadAccessKeylistId AND
> =A0 =A0tk.UserId IN (22943, 10899))
> WHERE mrt.MessageId =3D 72 AND
> =A0mrt.RevisionNumber =3D 1 AND
> =A0t.ReadAccessKeylistId IS NOT NULL;
>
> This is only a sub-query of a larger search query in my PHP application.
> MySQL workbench can't show query timings so I can'T say how long this
> part of the query takes. It's probably fast, but it is applied to ~600
> other rows to determine whether they should be included in the results
> or not.
>

Please post create table statements, show indexes and explain for any
queries with which you want help. Please then post each of your
attempted queries coupled with a written description of what you think
you are asking the database for.


--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Slow query, unknown why

Yves,
What happens if you replace the "tk.UserId IN (22943, 10899)" with
just one argument " tk.UserId = 22943".

Does it run much faster? If so, the In() statement may not be using an
index. You could try using a Union instead of In() to see if that is any
faster.
I have also found that if the tables used in the join are not going to be
updated, then I copy the rows to a Memory table and apply the appropriate
indexes and the joins will run 2x-3x faster.

Mike

At 11:12 AM 4/25/2010, Yves Goergen wrote:
>Hi,
>
>I'm still stuck with my SQL query that is slow but really shouldn't be.
>
>The problem is that I cannot create a simple test case. I could only
>provide you a whole lot of pages of PHP code and SQL queries to explain
>the problem.
>
>I have now three versions of my query. One with a sub select, which
>takes 40 ms and works. One with a left join instead, which takes 40 ms
>and works. And one with an inner join instead, which takes 3 ms and
>doesn't work. The number of left-joined rows should be around 5, so what
>can make it take 35 ms to join those handful of rows?
>
>MySQL server version is 5.0.67 and 5.1.41 (just updated).
>
>Here's a small impression of my query:
>
>SELECT t.TagId, t.TagName, tk.UserId
>FROM message_revision_tag mrt
> JOIN tag t USING (TagId)
> LEFT JOIN keylist tk ON -- Here's the left join
> (tk.KeylistId = t.ReadAccessKeylistId AND
> tk.UserId IN (22943, 10899))
>WHERE mrt.MessageId = 72 AND
> mrt.RevisionNumber = 1 AND
> t.ReadAccessKeylistId IS NOT NULL;
>
>This is only a sub-query of a larger search query in my PHP application.
>MySQL workbench can't show query timings so I can'T say how long this
>part of the query takes. It's probably fast, but it is applied to ~600
>other rows to determine whether they should be included in the results
>or not.
>
>--
>Yves Goergen "LonelyPixel"
>Visit my web laboratory at http://beta.unclassified.de
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org