PHP/MySQL slow_queries

Thread Tools Search this Thread
Top Forums Shell Programming and Scripting PHP/MySQL slow_queries
# 1  
Old 01-03-2008
PHP/MySQL slow_queries

Hi All,

I have a problem with my database having lots of 'stale' slow_queries. I think the problem may be because of the following code:

$numresults=mysql_query("select * from links where catagory=".$catagory." order by linknum");

I believe this sql statement is doing a full table scan; is this correct?

Would there be anything to gain from coding the sql like this:

select count(*) from links where ...

I've just realised that catagory (i know it's spelt incorrectly...) isn't an index... the links table is the biggest table - do you think this could be a problem?

Also I don't close the db connection - I'm reading conflicting messages about how good/bad this can be... most of my pages select a subset of data then have links where you can get the next/previous batch which work via post - i'm not sure how a close of each db connection will behave. Any ideas on the best approach?

My db is not very big. It only has six tables and 70k entries in the biggest table...

My index in the biggest table isn't contiguous because entries get deleted - is this a problem?

Lots of questions! Hope you can help Smilie

Many thanks, p.
# 2  
Old 01-03-2008
What are you calling "slow" lookups?

It is all depending on your setup/server, if you want uber-performance, consider this:

Cache Performance Comparison | MySQL Performance Blog

But I am assuming that you mean >10ms ... in which case, I would think to look at what else is jamming your CPU/RAM, because those queries should be pretty fast, even if you have to grab the whole DB...

Thats my 2 cents
# 3  
Old 01-04-2008
Hi Jjino,

Thanks for your reply - I've had a quick look over the link you supplied. I think the gist of what's being said is that you can cache the table lookup so you don't have to query the table again? Am I correct?

That's an angle I'll have to look at. My db was stuffed again this morning and I had to restart MySQL... considering I'm running a vBulletin forum on the same server and that's running fine I believe the server to be well up for the job... must be my code...

One other question: I'm only using one db user for all of the web page based lookups - is this normal practice?

Is there anywhere (apart from the MySQL docs pages) where I can learn about MySQL query building?

Many thanks,

# 4  
Old 01-04-2008
First to answer your question, yes MySQL does use some caching to make lookups faster. My example site was more pointing to how fast queries should run even without caching, but either way, I think you got the point.

Secondly, I would not doubt that your code is to blame, but I would doubt that it is the specific 2 lines that you had originally presented. From what I can tell, there should be no problem in issuing those two commands.

Something to try (general coding practice really) is to remove all the fluff, and make a direct query, starting with ALL links, and moving on to a category that you know the name of... AKA - remove the dynamic stuff to run a test.

If there is something "clogging" your MySQL application, its probably not queries, but lingering connections. Check to make sure that you dont accidentally loop through the mysql_connect() function. Also, if running many non-persistent connections, remember to use the mysql_close() function.

As for "guides" on how to do this all, you are left to the mercy of Google for anything complicated. Although I regularly reference The W3School's PHP Tutorial and another friendly DIY site tizag but neither of these compare to the completeness of the PHP/MySQL Function Docs

Hope some/any of this helps.
# 5  
Old 01-04-2008
Originally Posted by jjinno
Also, if running many non-persistent connections, remember to use the mysql_close() function.
Semi-anecdotally, I once was working on a Engineering Web-Server for EMC, creating various Databasing Applications... one of which I accidentally had forgotten to add the close function to... which after 5 hours of my development testing (aka change a bit and re-run) jammed up the Server with MySQL & forced them to do a hard reboot...

... thankfully I had an awesome boss, who just laughed at me in hysterics, and with a pat on the shoulder said "At least it was only Engineering. Imagine what you could do if you worked for IT!"
Login or Register to Ask a Question

Previous Thread | Next Thread

9 More Discussions You Might Find Interesting

1. Web Development

Can't Install MySQL with PHP

Hi, I'm on a Raspberry Pi with Raspbian Wheezy. I urgently need to get MySQL running with PHP, but I get an error. For example: $con=mysql_connect("","root","******","ids"); gives PHP Fatal error: Call to undefined function mysql_connect() So, I found I needed to install some... (2 Replies)
Discussion started by: FreddoT
2 Replies

2. Programming


Hello, While I was interpretation the PHP manual on database security the recent past, it said that you should by no means connect to the database as the super user but rather as one more user with more limited options. My question is: How do you generate new users and set access... (2 Replies)
Discussion started by: AimyThomas
2 Replies

3. Emergency UNIX and Linux Support

Migration of website... PHP/Mysql -which path for DB.php

Hi, I have two websites: and I didn't write either but have successfully moved all the files from to I (thought) I installed all the correct php modules and website2 is mostly up and running. However, my boss found that when we go to a... (15 Replies)
Discussion started by: Astrocloud
15 Replies

4. Shell Programming and Scripting

Mysql is not connected in php

Hi, The php is not able to connect into my mysql database. But i can able to connect by manually. I think that I have missed some points. Please guild for the same. Thanks, Mani (1 Reply)
Discussion started by: Mani_apr08
1 Replies

5. Programming


Hello every one i have question i want to build DATAbase using PHP as interface i use shell to access to linux . i have in linux psql and SQLplus i'll call all html files that has db tabels from shell directory. what should to do before design php pages. can build the database sql design... (3 Replies)
Discussion started by: Scotch
3 Replies

6. Shell Programming and Scripting

Something went awfully wrong in PHP+MySQL :(

When I'm running a few statements through mysql console, they are working just fine but when I'm trying to write some logic in php and trying to execute it, its awfully slow.. Its perhaps performing, 10 queries in 20 seconds... :( Why is this happening? My table structures are: Name:... (23 Replies)
Discussion started by: Legend986
23 Replies

7. Shell Programming and Scripting

Problem with PHP and MySQL

Okay, I'm new to this PHP and MySQL stuff, so help would be VERY much appreciated. :) On my iMac runnning Panther, it has MySQL and PHP installed. Yet when I view a PHP file from the iMac or another computer at my house, I get the source code. What's wrong? (11 Replies)
Discussion started by: Danny_10
11 Replies

8. UNIX for Dummies Questions & Answers


I want to design a database, using mysql as a backend, and PHP as the frontend, I wanna be able to easily build forms in PHP to communicate with MySQL, is there any programs that will allow this, I really dont want to program all the forms by hand.. thankyou (2 Replies)
Discussion started by: kwalick
2 Replies

9. Cybersecurity

mysql php

with a limited knowledege of php and sql, what is a good and secure way to do passwords running an https server? (1 Reply)
Discussion started by: macdonto
1 Replies
Login or Register to Ask a Question