MySQL Forums
Forum List  »  Optimizer & Parser

mySQL performance problem on subqueries
Posted by: Falcon Meriva
Date: May 15, 2006 05:14AM

Hi,

I have table like this;

-----
C001 BIGINT(22) UNSIGNED ZEROFILL AUTO_INCREMENT INDEX
C002 BIGINT(22) UNSIGNED ZEROFILL INDEX
C003 BIGINT(22) UNSIGNED ZEROFILL INDEX
C004 BIGINT(22) UNSIGNED ZEROFILL INDEX
C005 BIGINT(22) UNSIGNED ZEROFILL INDEX
-----

I simply write a query like this,

-----
SELECT
T2.*,
(
SELECT
COUNT(*)-1 AS count
FROM
`myTable`
WHERE
C005 = 121 AND C003 BETWEEN T2.C003 AND T2.C004
) AS depth
FROM
`myTable` AS `T2`
WHERE
T2.C005 = 121
ORDER BY T2.C003;
-----

The query works fine for the table format. It sends the records as wanted. ( In this query it'll send me about 2377 rows )

But there was a big problem about the query. The problem is queries time. It took about 25 seconds to end!

I don't understand why subselecting using count values by fetched rows make this query's time long. In normal life counting a lots of rows takes about 0.00001 seconds.

Any idea?

Options: ReplyQuote


Subject
Views
Written By
Posted
mySQL performance problem on subqueries
2623
May 15, 2006 05:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.