A very large phpmyadmin database issue...

[Guest]

Hello

Has anyone any suggestion how I can get around this phpmyadmin issue I'm having.

I'm using CS3 on a Mac.

Basically the phpmyadmin database stores 'mobile phone offer' details for a phone retailer and it's rather large - over 190,000 entries.

On the homepage of the retailers website I've put in a form, where you select a 'Network' and a 'Make' to narrow the entries down - i.e. select 'Vodafone' and 'iPhone' and click the submit button to go to a results page showing only 'iphones' available from 'Vodafone'.

The problem I have is because of the size of the database it takes over ten minutes to load the results page, obviously far too long.

Are there ways / techniques to speed up this process?

The form submits the 'Network' and 'Make' choice via GET and the results page is set up like...

SELECT * FROM database
WHERE Network = colname AND Make = colmake
ORDER BY `Monthly Line Rental` ASC

The colname and colmake link to GET results in the usual way.

Any help would be great

[David_Powers · Accepted solution]

Judging from your SQL query, the network and make values are stored in the same table. This is highly inefficient, and is likely to lead to input errors, such as Vodafone and Vodaphone. Repeated information should be in separate tables, using foreign keys to link them to the other table. Searching with numeric foreign keys is much quicker, because databases search numbers far more quickly than text.

However, even with the current setup, you should be able to speed up the results process by adding an index to the Network and Make columns.

[pziecina]

Hi

In addition to Davids answer.

You are collecting all the information stored in the database, do you really need all the information? It is much faster to only collect the relevant/required info, (such as make, model, network) and then to do a separate search when the relevant item is clicked for all the info regarding that model.

PZ

[bregent]

>it's rather large - over 190,000 entries.

I would consider this to be a very small table. You should not be having performance issues of that magnitude with that few rows. As David suggested, if possible make sure you have an index on all columns that will be searched. But you may also be on an underpowered server - check with your host.

But I'd be surprised if adding a performance index does not resolve the problem.

[Guest]

Thanks for the help guys.

Indexing the searched coulumns has worked, now just takes a split second to search!