slow query
------_=_NextPart_001_01C67941.25F28A08
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
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.
------_=_NextPart_001_01C67941.25F28A08--
Re: slow query
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"
<ipalanisamy [at] 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=B0) =
jtgalkowski [at] 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 [at] m.gmane.org
RE: slow query
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 [at] consentry.com]
Sent: Wednesday, 17 May 2006 9:04 AM
To: win32 [at] 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.
--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32 [at] m.gmane.org
