On Sun, Apr 25, 2010 at 9:12 AM, Yves Goergen <nospam.l...@unclassified.de> 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. >
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. -- Rob Wultsch wult...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
