slow query

am 17.05.2006 01:33:30 von Ilavajuthy Palanisamy

Re: slow query

am 17.05.2006 01:58:42 von Jan Theodore Galkowski

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"
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.
>
>
>
--=20
Jan Theodore Galkowski (o°) =

jtgalkowski@alum.mit.edu
http://tinyurl.com/qty7d

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: slow query

am 17.05.2006 02:31:40 von jbonnett

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:ipalanisamy@consentry.com]=20
Sent: Wednesday, 17 May 2006 9:04 AM
To: win32@lists.mysql.com
Subject: slow query

Hi,

=20

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

=20

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 |

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

=20

There are approx 20K distinct userid are available.

=20

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

=20

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

=20

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

=20

I'm using MYSQL 4.1 on Windows.

=20

Is there any way to make this query faster?

=20

Ila.

=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org