MySQL Query timesout first time around - Shows on Refresh

[LazyD]

Im working on a site where im dealing with a poorly put together database consisting of about 400,000 rows.

Everytime I make a call to the database, the PHP script maxes out its execution time (Set to 30 seconds) - When I refresh it works fine... Obviously this is kind of an issue when the site is put online (Currently, im working with it locally on WAMP)

I think part of the issue is for the main portion of the site, im having to issue 2 DB queries, 1 is for the results that are actually spit out, which are limited to 50 per a page, then a second one is queried to get the total number of rows that apply so I can page the results...

Is there anything I can do to speed up the calls? A sample call to the DB looks like this:

This actually gets the results that will be displayed:
SELECT DISTINCT `rname` FROM `realbiz` WHERE SUBSTRING(rname, 1, 1) = '$letterGet' LIMIT $offsetResults, $pageResults

This gets the total results to use for paging:
SELECT DISTINCT `rname` FROM `realbiz` WHERE SUBSTRING(rname,, 1, 1) = '$letterGet

Thanks...

 

[casidnet]

Are you using any indexes? The substring will kill your performance over 400K rows. The refresh probably works because it is cached.

You could add another column to the table with just the first letter and index that. I bet that would bring your query down to the sub-second range but then you have integrity issues and it's not really clean but these are the tradeoffs we make

 

[LazyD]

casidnet - I was thinking of doing something similar to what you suggested by going through every result, using substr to grab the first character and insert it back into the DB for its respective row..

 

[LazyD]

wow...

I did the substr to grab the first letter, which took a long god damn, made an index out of it and 1 other row and holy shit.. the site is much faster and doesnt time out at all...

rep to you..