Explain select gives me this table type possible_keys key key_len ref rows extra template range PRIMARY PRIMARY 4 NULL 55 using where
surpisingly it never runs slow when i run the query manually, also when this query runs for long, mytop or show full process list shows that other queries are running just fine, infact in the time that this query is running many similar queries are excecuted very fast.... Thanks Kishore Jalleda On 8/9/05, Mike Wexler <[EMAIL PROTECTED]> wrote: > Kishore Jalleda wrote: > > Try two things. First: > > EXPLAIN > > SELECT title, template > FROM template > WHERE templateid IN > (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); > > Second, when its running slow, do a > > SHOW FULL PROCESSLIST > > I suspect another, longer running query is running at the same time and has > the table locked. > > > > > >Hi All, > > We have a query that sometimes takes 10-15 seconds to run, > >atleast thats what mytop or the slow log say, usually it runs in less > >than 0.04 seconds.... > > > >SELECT title, template > > FROM template > > WHERE templateid IN > >(608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); > > > >templateid is the primary key, also there are only 500 rows in the > >table "template", > >(mysql version 4.0.17 on Redhat 7.3 ) > > > >whats making the query to run slowly, > >the server only runs at less than 10% cpu utilization, has lots of > >ram and currently averages 120 qps ( 10M/day) , and is very fast 99% > >of the time except for this query... > > > >also the temp_table_size is set to 128MB, inspite of this the ratio > >for Created_tmp_disk_tables/Created_tmp_tables is close to 0.1, could > >this be a reason, why is Mysql creating soo many temp tables on the > >disk.... > > > >Thanks a lot and any help would be appreciated .. > > > >Kishore Jalleda > > > > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]