Need more speed - how to speed up my code

ChetUbetcha

I have a large database with 11,000 items and growing. Unfortunately, there have been lots of changes over the last 3 years. There are now 61 columns in one particular table. There are a number of variables that reference other tables. Examples: there are at least 10 items that reference tables with at least 12 items each. The main table will simply reference the ID number on the other table.

My main issue is converting these IDs to text when I try to export to screen or Excel. It takes WAY too long. I have small (I think) functions that convert the ID number from the main table to the text in the referenced table:

function getLocation($requiredData) {
	$result = mysql_query("SELECT * FROM site_location WHERE id = '$requiredData'") or die(mysql_error());  

	while($row = mysql_fetch_array($result)) {
    return $row['value'];
	} 
}

Now when I try exporting, it has to go through all these lines and convert the items that are referencing the smaller table. Should I run the function once at the beginning, let it create variables and simply replace the ID with the respective text each time it runs across one?

Sorry if this sounds confusing - drinking decaf for some unknown reason right now.

NogDog

Probably not enough info for us to say anything definitive, but for the query you posted, if you are only going to use the 'value' column, then specify just that column in the SELECT list instead of retrieving all columns via "*". Also, make sure the "id" column is indexed in order to optimize your WHERE statement.

PS: 11,000 items is not really a large database as databases go, though if poorly designed it's big enough to be noticeably slow, I suppose. On the other hand, 61 columns are a lot for one table, and suggests that there may be a need for some normalization/redesign. 😉

johanafm

Also, unless id is actually a text type, you shouldn't be comparing it to text. If it's an integer, you compare it to integers

-- yes
WHERE id = 1234

-- no
WHERE id = '1234'

Is the id set as primary key? If id is a candidate key and isn't primary key and you have no other primary key, then create primary key (id). If either of those things aren't true, you add an index for it instead.