MySql on 2nd PC

[bzzrd2]

I'm running MySql as localhost on my PC. I find it somewhat slow to work with. I have a second machine on WiFi in another room. Would there be an advantage to running MySl on the 2nd machine. If so, how would I configure it? Short of that I'm considering a Dual Core processor for mycurrent unit. Thanks.

[oracleguy]

Well the advantage being it would move any sort of data processing from the computer it is on to the other one. You would pretty much just install and configure it on there like you did on the current machine. And then when you connect to your database, instead of saying "localhost" for the server name you'd specify the other computer's name or IP address.

What kind of computer is it running on now?

[bzzrd2]

I have a P4 with a gig of RAM.

[CFMaBiSmAd]

Before you do this, be sure that the slowness is not due to a lack of indexed tables or poorly written queries.

When the mysql server is running on the same computer as the client making the queries, the data is transfered directly through memory between the mysql service and the client. If you instead do this through your wireless network, the transfer rate will be several orders of magnitude slower. So, if you are transferring large amounts of data into and out of the database per query, the speed will suffer due to the transfer rate and if the slowness is actually due to table design and query design, you will still suffer from the additional time it takes mysql to perform the query.

[oracleguy]

Yeah, unless there is a LOT of other stuff running on the computer MySQL shouldn't be running slow. And since its a relative term, what is slow? Like are queries taking almost a second to complete or something?

[bzzrd2]

More like 4 - 5 seconds sometimes. I even put a new partition on the C drive and installed only the necessary stuff. No v-scan, no nothing. Still the same. It's almost as slow as my crappy host. Using Firefox gives it a marginal improvement. Oh, and the second computer did not pan out either. I'm Using MySql 5 and Apache. Lightning fast to view a page. Not so fast doing queries. Memory use is at about 30% and CPU goes to around 8 - 10%. I have been thinking about going with a dual core processor. Maybe it's time?

[bzzrd2]

This AM I actually had a 2 word query take almost 10 seconds. I was thinking...maybe I'm trying to shove a golf ball down a garden hose. Rather than dual core processor ( cpu cycles sre good) what about a serial ATA board with a 10K rpm HD. Would that not make sense to get more data through...faster?

[firepages]

I very much doubt your problems are hardware related, I have a 500Mhz Sun NetraT1 in front of me and it runs (simple joins) queries on quite large tables without blinking.

Are your mysql tables indexed ? & how are you connecting to MySQL ?

[bzzrd2]

As far as I know the tables are indexed properly. I purchased the program and the writer says I will have no proplem with many thousand querys. It ran OK on my last host will 24,000 lines. I'm using XAMMP for Apache and MySql on localhost. I have no doubt that the new provider I'm using is terrible as I went back to the old (had some time left) and all was fine. (fast) I built the databse directly on there but it somehow got corrupted on my hard drive so I'm going from scratch again. The table looks like:

place
select from many places
select from A-Z
Select Place
Select A-Z
Select Place

Some have a lot of places. Some, only a few.

There are other small tables but this will be the largest.

[bzzrd2]

I found the problem! As categories are entered into the database they are pulled back to dropdowns "From Category" to To Category". Like the name says it allows you to take a field and switch it to another category etc. I think what is happening is, with every input is it pulling back all of the data. That would also explain my #1226 - User 'old_friends' has exceeded the 'max_questions' resource (current value: 50000) after only a few queries. Thanks guys!

[bzzrd2]

Just out of curiosity, what would be the max categories would one expect to run well with this structure for "categories": Hop I explained that right. Inititially I will have 23,000 places and then I need to go up to around 250,000.

Category:
-Select Place (ie: country)
-select from many places (ie: state)
-select from A-Z
-Select Place (ie: city) - (Many)
Select A-Z
Select Place -(ie:Office) (Many)

CREATE TABLE `tf_categories` (
`id` bigint(20) NOT NULL auto_increment,
`cat_name` varchar(255) default NULL,
`pid` bigint(20) default NULL,
`clicks` bigint(20) default NULL,
`sb_fee` decimal(10,2) NOT NULL default '0.00',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3766 ;

--
-- Dumping data for table `tf_categories`
--

INSERT INTO `tf_categories` (`id`, `cat_name`, `pid`, `clicks`, `ts_fee`) VALUES
(1, 'Canada', 0, 0, 0.00),
(2, 'U.S.A.', 0, 0, 0.00),
(3, '* Select a Province', 1, 0, 0.00),
(4, 'Alberta', 1, 0, 0.00),
(5, 'British Columbia', 1, 0, 0.00),
(6, 'Manitoba', 1, 0, 0.00),
(7, 'New Brunswick', 1, 0, 0.00),
(8, 'Newfoundland', 1, 0, 0.00),

AND SO ON
AND SO ON

(17, '* Select a City in Wherever Starting With:', 4, 0, 0.00),
(18, 'A', 4, 0, 0.00),
(19, 'B', 4, 0, 0.00),
(20, 'C', 4, 0, 0.00),
(21, 'D', 4, 0, 0.00),
(22, 'E', 4, 0, 0.00),
(23, 'F', 4, 0, 0.00),
(24, 'G', 4, 0, 0.00),
(25, 'H', 4, 0, 0.00),
(26, 'I', 4, 0, 0.00),
(27, 'J', 4, 0, 0.00),
(28, 'K', 4, 0, 0.00),


AND SO ON AND SO ON



(49, '- Select Buildings City-', 18, 0, 0.00),
(50, 'ACADIA', 18, 0, 0.00),

AND SO ON


(51, '- Select Your Building -', 50, 0, 0.00),
(52, 'Acadia Building', 50, 0, 0.00),
(57, 'Hichem Building', 50, 0, 0.00),
(60, 'Salm Salem Building', 50, 0, 0.00),
(61, 'Selman Riza Building', 50, 0, 0.00),

[firepages]

CFMaBiSmAd, sorry I only just spotted your original post, was not trying to double up on you!


(17, '* Select a City in Wherever Starting With:', 4, 0, 0.00),

I don't understand why this is in the same table as the data .. or is it ? if so then it looks wrong straight away but since I have no idea what your app is or does so thats an uneducated comment.

OK, now I see pid is probably the parent id & I am starting to get it....
(17, '* Select a City in Wherever Starting With:', 4, 0, 0.00),
&&
(18, 'A', 4, 0, 0.00),
should not be in the same table as (what I assume is) the real data
(50, 'ACADIA', 18, 0, 0.00), etc

so IF buildings are the endpoint (the data we are actually searching for) then they should be in their own table...
(e.g.)
b_name, b_city, b_state, b_country, b_clicks, b_ts_fee

so b_city,b_state,b_country are all indexes in separate lookup tables in countries, cities & states tables and the A,B,C , 'select * from' etc would not be in the database at all

not sure how far you are with this but I think you need to rethink this if you have a potential concurrent user base in the future.