problem w/ basic "select" query

[dcayce]

🙁 when i run a “select * from myTable” it takes a very long time to get a result set back. The table has less than 14K rows and is located on a server connected to my box via 10/100 lan through a Netgear wireless router. I don't believe the network to be at fault since I have the same table schema and data in a Microsoft 2000 sqlServer on the same server box and queries ran from Microsoft Enterprise Manager return a result set in under 1 second. Insert, updates & deletes and extremly fast in SQLyog, much faster than in Microsoft Enterprize Manager.

Do I have mySQL or SQLyog setup improperly ?

Thanks in advance for the help

Don

[CalEvans]

14k rows is a lot of info no matter what your connection is. I've got one table on my local lan (100k) that takes 30 seconds to select 5k rows. Why? Because I've got 2 honkin big text fields in it and moving that kind of data takes time.

I would suggest that there are very few legitimate reasons for selecting all fields, all data on a 14k row table. Narrow your select statement down and only select the fields you need instead of select *.

If you are backing the database up then I would suggest you use a tool like mysqldump instead as it's faster. (Because it does not have a graphical interface.)

=C=

[dcayce]

You are correct in saying that one does not normally run a query with the intended result set being the entire table. However, when populating a table for the first time, which is my case, it is advisible to at least give all of your data a “is this what I want” look see.

The question still remains, however, why is Microsoft Enterprise Manager faster ? Is it because it starts displaying the query results before it has finished creating the result set. What would you suggest as a pratical limit, row wise, for a returned result set be. Would result sets of three – five hundred be to large in your estimate. Since this server will be going onLine in a two – three weeks I have very little time to test and tune.

Don't mean to ramble and thanks for the advice

[Shadow]

Which version of MySql and SQLyog do you use? How did you configure your MySql server?

[dcayce]

server is configured as follows

OS : NT Server 2000 Advanced

webServer : Tomcat v5.0.12

Java : java sdk v1.4.2

mySQL: v5.0.1

SQLyog: v3.71

mySQL setup with default settings

If you need more info just let me know

dcayce

[Shadow]

Perhaps you should fine tune the settings in my.cnf/my.ini to get better performance. The default settings are not optimised to support large dbs. SQLyog does not have any setting that would affect query performance.

Increase read_buffer_size and configure the query cache, first of all.

[Author]

Posts