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=arch...@jab.org

Reply via email to