MySQL + PHP

Anon

Hi All,
Generally, I have two questions and seeking advice from you.

Is the following situation can be done by PHP/MySQL?
"1st user make a MySQL query for a term, it run faster when 2nd...nth user make the same query in the MySQL database ...."
Dual PIII 733 + 512 Mb RAM + 9.1GB Ultra 160 SCSI disk. Apache 1.3.12, PHP4.01pl2 and MySQL 3.22.32. It returns result within 2-3 second for 200000 rows database. Is it Normal?
How to speed up the performance? e.g. Apache config and php.ini cpnfig..?

Thanks for your interest in these questions.

Anon

One more note:
Can ZEND help these situations much?
KK

Anon

Hi KK,

I think that this already is a feature of MySQL. The second,third ... runs of a query are often faster than the first one.
The only way (having compiled mysql already, because mysql performance depends on how you compile it) to speed up your queries is to optimize your table layout and/or queries if possible.
It's a good idea to use indices since they speed up your queries a lot.
Check if you can combine queries in a script to one query using joins. It's always better to run one query instead of two or more queries to get your data.

The mysql manual contains a chapter about getting maximum performance out of mysql.
The chapter not only covers queries but compile time and file system issues.

Greetings,
Thomas

Anon

When a database runs a query, it tries to load all relevant tables etc into memory. The first time this will be slow. The second time, the data should still be in memory.
The speed of MySQL depends partially on how you compile it. There are a few ways of compiling that guarantee a slow server, but those are rare.
For me, the basic RPM distribution worked faster than compiling it with all the "fast" options. But I'm only me.

You can further increase speed by fiddling with the configuration.
Large amounts of memory do wonders for database performance.

In your particular instance, 200k rows in 2-3 seconds sounds slow, but what kind of query is it? are you looking for 1 row in 200k or all 200k of them?
and how big is each row?
MySQL slows down dramatically if your query returns lots of records.
Fetching 1 record is fast, fetching 200k will be slower.

Anon

Hi Vincent and Thomas Sinka!!!

You guys are so Friendly and nice!
I learn a lot from your posts!!
Really Thx phpbuilder.com give me chance to learn from experts.

Back to the questions. ^.~

Since it was compiled, and improvement in compiling level seems so late, but I will try it later.

Anyway, thanks!!

Anon

Hi Vincent and Thomas Sinka,

Could you mind to give me example(s) what I should to make compilation of MySQL?

I compile it just by using:
./configure --prefix=/usr/local/mysql

make

make install

I saw "static" in MySQL manual, is it related??

Thanks a lot, as I want to reinstall all thing again...