3000 rows, 33 seconds, then 67 seconds

------=_NextPart_000_0005_01C4DE2A.D491BC90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I did a query across our lan today from our web server. Basically I ran =
a query pulling out the first 3000 rows in a table. There is a mix of =
ints, tinyints and varchars, and a couple of text fields. I didn't even =
use a where clause in my query. First it took 33 seconds, then I ran the =
same query and it took 67 seconds.

Why is this taking so long, isn't 3000 rows supposed to be rather fast? =
There is not really any hectic amounts of data in each row.

Server is a P4 2.8 with 1 Gig ram, running Windows 2003 Server. Query =
was over a 100mbit LAN.

Any ideas?
Greg




------=_NextPart_000_0005_01C4DE2A.D491BC90--

Re: 3000 rows, 33 seconds, then 67 seconds

It usually takes me 1 seconds per thousand to do an INSERT with the same
spec server.

James Mackie
Appiam Ltd
Developer
www.appiam.com
james@appiam.com
07884 494 333

----- Original Message -----
From: "Greg Quinn"
To:
Sent: Thursday, December 09, 2004 6:08 PM
Subject: 3000 rows, 33 seconds, then 67 seconds


I did a query across our lan today from our web server. Basically I ran a
query pulling out the first 3000 rows in a table. There is a mix of ints,
tinyints and varchars, and a couple of text fields. I didn't even use a
where clause in my query. First it took 33 seconds, then I ran the same
query and it took 67 seconds.

Why is this taking so long, isn't 3000 rows supposed to be rather fast?
There is not really any hectic amounts of data in each row.

Server is a P4 2.8 with 1 Gig ram, running Windows 2003 Server. Query was
over a 100mbit LAN.

Any ideas?
Greg





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

Re: 3000 rows, 33 seconds, then 67 seconds

Greetings,

Are your tables indexed? A full scan is always slower...
Try indexing your table by an unique indentifier.

Best regards,

On Thu, 9 Dec 2004 18:11:49 -0000, James - Developer wrote:
> It usually takes me 1 seconds per thousand to do an INSERT with the same
> spec server.
>
> James Mackie
> Appiam Ltd
> Developer
> www.appiam.com
> james@appiam.com
> 07884 494 333
>
>
>
> ----- Original Message -----
> From: "Greg Quinn"
> To:
> Sent: Thursday, December 09, 2004 6:08 PM
> Subject: 3000 rows, 33 seconds, then 67 seconds
>
> I did a query across our lan today from our web server. Basically I ran a
> query pulling out the first 3000 rows in a table. There is a mix of ints,
> tinyints and varchars, and a couple of text fields. I didn't even use a
> where clause in my query. First it took 33 seconds, then I ran the same
> query and it took 67 seconds.
>
> Why is this taking so long, isn't 3000 rows supposed to be rather fast?
> There is not really any hectic amounts of data in each row.
>
> Server is a P4 2.8 with 1 Gig ram, running Windows 2003 Server. Query was
> over a 100mbit LAN.
>
> Any ideas?
> Greg
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: http://lists.mysql.com/win32?unsub=danieldaveiga@gmail.com
>
>


--
Daniel da Veiga
Computer Operator - RS - Brazil

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

Re : 3000 rows, 33 seconds, then 67 seconds

------=_NextPart_000_000C_01C4DE2F.9793C6D0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Yes, they are indexed. I did a scan without a WHERE clause because I =
didn't want any indexes involved, surely a scan to return the first 3000 =
rows in a table is more efficient than returning 3000 rows in a table =
with a WHERE clause?

With regard to having a WHERE clause in the query, my table basically =
contains all the emails in all users Inbox folders. My UserID primary =
key is a GUID, stored in a varchar field. I was wondering how efficient =
this would be compared to giving my users UserID an int field???

Greg


>Greetings,

>>Are your tables indexed? A full scan is always slower...
>>Try indexing your table by an unique indentifier.

>>Best regards,

On Thu, 9 Dec 2004 18:11:49 -0000, James - Developer =
wrote:
> It usually takes me 1 seconds per thousand to do an INSERT with the =
same
> spec server.
>=20
> James Mackie
> Appiam Ltd
> Developer
> www.appiam.com
> james@appiam.com
> 07884 494 333
>
------=_NextPart_000_000C_01C4DE2F.9793C6D0--

Re: Re : 3000 rows, 33 seconds, then 67 seconds

The quickest way to find out what's going on is to use the EXPLAIN
keyword on your query to see how the query is going to be executed --
then you can choose indexes appropriately


On Thu, 9 Dec 2004 20:42:26 +0200, Greg Quinn wrote:
> Yes, they are indexed. I did a scan without a WHERE clause because I didn't want any indexes involved, surely a scan to return the first 3000 rows in a table is more efficient than returning 3000 rows in a table with a WHERE clause?
>
> With regard to having a WHERE clause in the query, my table basically contains all the emails in all users Inbox folders. My UserID primary key is a GUID, stored in a varchar field. I was wondering how efficient this would be compared to giving my users UserID an int field???
>
> Greg
>
>
>
>
> >Greetings,
>
> >>Are your tables indexed? A full scan is always slower...
> >>Try indexing your table by an unique indentifier.
>
> >>Best regards,
>
> On Thu, 9 Dec 2004 18:11:49 -0000, James - Developer wrote:
> > It usually takes me 1 seconds per thousand to do an INSERT with the same
> > spec server.
> >
> > James Mackie
> > Appiam Ltd
> > Developer
> > www.appiam.com
> > james@appiam.com
> > 07884 494 333
> >
>


--
John Paul Ashenfelter
CTO/Transitionpoint

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

Re: Re : 3000 rows, 33 seconds, then 67 seconds

A direct table scan can take longer than an index scan if phisical
space is too fragmented.

Can you test your query in the box where mysql lives? Just to be sure is
not a net problem. A faulty net adapter can be losing information,


Greg Quinn escribió:

>Yes, they are indexed. I did a scan without a WHERE clause because I didn't want any indexes involved, surely a scan to return the first 3000 rows in a table is more efficient than returning 3000 rows in a table with a WHERE clause?
>
>With regard to having a WHERE clause in the query, my table basically contains all the emails in all users Inbox folders. My UserID primary key is a GUID, stored in a varchar field. I was wondering how efficient this would be compared to giving my users UserID an int field???
>
>Greg
>
>
>
>
>>Greetings,
>>
>>
>
>
>
>>>Are your tables indexed? A full scan is always slower...
>>>Try indexing your table by an unique indentifier.
>>>
>>>
>
>
>
>>>Best regards,
>>>
>>>
>
>On Thu, 9 Dec 2004 18:11:49 -0000, James - Developer wrote:
>
>
>>It usually takes me 1 seconds per thousand to do an INSERT with the same
>>spec server.
>>
>>James Mackie
>>Appiam Ltd
>>Developer
>>www.appiam.com
>>james@appiam.com
>>07884 494 333
>>
>>
>>


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

Re : 3000 rows, 33 seconds, then 67 seconds

------=_NextPart_000_0003_01C4DE34.AA559E60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Its definately not a network prob, cached queries of 3000 rows are =
returned almost immediately.
Its just the initial read that is very slow.

How do you mean physical space? You mean if hard drive space is =
fragmented?

>>A direct table scan can take longer than an index scan if phisical=20
>>space is too fragmented.

>>Can you test your query in the box where mysql lives? Just to be sure =
is=20
>>not a net problem. A faulty net adapter can be losing information,
------=_NextPart_000_0003_01C4DE34.AA559E60--

Re: Re : 3000 rows, 33 seconds, then 67 seconds

MySql manual says:

========================


5.7.2.10 Table Optimization




To coalesce fragmented records and eliminate wasted space resulting from
deleting or updating records, run @command{myisamchk} in recovery mode:

shell> myisamchk -r tbl_name

=============================

I don't know if you are using myisam or innodb tables. Search for
"fragment" in MySql Manual.

Of course, if disk space is fragmented too you will need to get rid of
that too.

Greg Quinn escribió:

>Its definately not a network prob, cached queries of 3000 rows are returned almost immediately.
>Its just the initial read that is very slow.
>
>How do you mean physical space? You mean if hard drive space is fragmented?
>
>
>
>>>A direct table scan can take longer than an index scan if phisical
>>>space is too fragmented.
>>>
>>>
>
>
>
>>>Can you test your query in the box where mysql lives? Just to be sure is
>>>not a net problem. A faulty net adapter can be losing information,
>>>
>>>


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