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:Jan Theodore Galkowski (jtga@alum.mit.edu)
Date:05/16/2006 04:58:19 PM
List:com.mysql.lists.win32

IMO, Ila, y'all need more structure in your data design and storage, anticipating these questions that need fast answers. Can't just lob data into a haystack and expect to be able to find it without computational work.

- jtg

On Tue, 16 May 2006 16:33:30 -0700, "Ilavajuthy Palanisamy" <ipal@consentry.com> said:

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.

-- Jan Theodore Galkowski (o°) jtga@alum.mit.edu http://tinyurl.com/qty7d