Shell Programming and Scripting

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:

Code:

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

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

Many thanks, p.

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

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,

p.

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.

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!"