3 messages in com.mysql.lists.win32RE: slow query
FromSent OnAttachments
Ilavajuthy Palanisamy16 May 2006 16:33 
Jan Theodore Galkowski16 May 2006 16:58 
jbon...@sola.com.au16 May 2006 17:31 
Subject:RE: slow query
From:jbon...@sola.com.au (jbon@sola.com.au)
Date:05/16/2006 05:31:17 PM
List:com.mysql.lists.win32

What client and client connection (ODBC, .NET Connector, C library etc.) are you using? Your hardware/network environment may also be a factor.

With that number of records you should expect it to take a while, but 40 mins seems a bit much!

John B.

-----Original Message----- From: Ilavajuthy Palanisamy [mailto:ipal@consentry.com] Sent: Wednesday, 17 May 2006 9:04 AM To: win@lists.mysql.com Subject: slow query

Hi,

I have a merged table with 35 million records, the below query takes around 40 mins to return.

mysql> explain select distinct userid from mfs ;

+----+-------------+-------+-------+---------------+------------------+- --------+------+----------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------------------+- --------+------+----------+-------------+

| 1 | SIMPLE | mfs | index | NULL | mfs_userId_Index | 8 | NULL | 35539364 | Using index |

+----+-------------+-------+-------+---------------+------------------+- --------+------+----------+-------------+

There are approx 20K distinct userid are available.

The show processlist stays in 'Sending Data' state for the complete query period (i.e. 40 mins).

502 | root | localhost:4166 | webnmsdb | Query | 361 | Sending data | select distinct userid from mfs

If I add a limit say limit 100 then it returns in 8 secs.

I'm using MYSQL 4.1 on Windows.

Is there any way to make this query faster?

Ila.