Hi Guys,

Just wondering..

Why is it that when you ( mysql -uUSERNAME -pPASSWORD )
and then run a query.
Its like UBER quick and uses hardly any CPU.. ( 0.00 sec )

However the same query thats run through PHP takes like 1second and uses 99% cpu when it happens?

Ive just noticed this and wonders if these some odd setting in php misconfigured?

Perfectly usual.

Running the query directly from a command line will always be quicker than calling the same query coded in PHP or in phpmyadmin as there is no overhead.

PHP has to parse the input string, pass it to the SQL server, wait for the response, return to the php process and format the data for the output.

Originally Posted by chrishirst

Perfectly usual.

Running the query directly from a command line will always be quicker than calling the same query coded in PHP or in phpmyadmin as there is no overhead.

PHP has to parse the input string, pass it to the SQL server, wait for the response, return to the php process and format the data for the output.

Dont suppose you know if any techniques to speed this up?
While its not an issue yet, It would be good to know for later.

It all depends on the type of queries you are running.

Query caching can help.
Using stored procedures.
Optimising queries by using a field list rather than "SELECT * FROM".
Filtering and/or sorting in the database rather than in server side code.

To be honest implementing code and queries that are optimised for speed is much easier from day one than it is to "reftro fit" when and if it becomes a neccessity.

This is something i did try But only recently found some bad queries

For example: ( using * )
SELECT DISTINCT * from visitorips where siteid = '$siteid' order by id desc limit 30
Takes about 1second to execute. ( Edit: Sorry i mean 5secs.. )

HOWEVER!
If i change the where from = to "like"
SELECT DISTINCT * from visitorips where siteid LIKE '$siteid' order by id desc limit 30
It states it takes 0.00 seconds and works fine.
( I then have some PHP code that checks the siteids "really" match after.

This has increased performance by like 700%

I suppose i should change it from * to the fields..

Ive optimized the hell outta every other query i could find.
I cant stand seeing something take longer than 0.00 sec.
lol

There is no "right way" to optimise queries as it depends to some extent on the type of data and the table structures.

And of course with MySQL the storage engine can introduce bottlenecks.

For example; MyISAM uses "table locking" during updates which will delay other queries, whereas InnoDb uses record (row) locking. So if your script/application does a lot of writing to the db InnoDb should be your engine of choice.

Testing the various options with real data is the way to learn what works best for various scenarios/schemas.

Cool, Thanks for the insight.
Im not DBA so still learning.. Then again that's inherent when working in I.T...

Id say I do about 98% Selecting and 2% ( maybe less ) doing updates and deletes.
My engine is MyISAM so should do for the moment.

Thanks again man.

Ive also more than halfed my server load from sorting my queries out. ( System gets about 50k hits a day )

So rather than clustering the frontend further .. This has helped a lot.

Originally Posted by chrishirst

Perfectly usual.

Running the query directly from a command line will always be quicker than calling the same query coded in PHP or in phpmyadmin as there is no overhead.

PHP has to parse the input string, pass it to the SQL server, wait for the response, return to the php process and format the data for the output.

You missed an overhead with PHP i.e. connection to database.