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" <nospam.l...@unclassified.de>
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=mo...@fastmail.fm
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
