I had a code written long time ago which was working fine for small records but as the number of records begin to increase it completely failed. I am attaching the file with comments which will help you understand the problem.

For fewer records you can see it working at http://dssln.info/search_logistic_providers.php
For large records you can see its behaviour at http://216.14.120.171/search_logistic_providers.php

Basically this code depends on three tables
1) transport
2) services
3) servinv

transport table contains detail of different transport companies and has about 20,000 records
services table stores different services which can be provided by transporters and has 235 records
servinv maps each transporter to each service and tells whether a specific transporter offer this service
or not and it has about 4,233,841 records.

My code completely fails to handle such a large number of records so I need some one to help me optimize
my queries to make it work

Member Avatar for diafol

Sorry ain't got the time to wade through, but are your tables indexed? Indexing should significantly improve performance.

Hi ardav,

Thanks for the response. Yes all these three table are indexed.

Don't use * anywhere. Define the columns you need for every query. It will help with performance a little.

On your query to get the number of rows for pagination, you are selecting all the data for no reason. Use count(*) it will return a record immediately because mysql caches the number of rows.

That should help out the most. That way you are only selecting 25 rows at a time. Not the 4,000,000+.

Thankyou very much for the reply. Can you please explain a little more how can I use count in this perticular case.

Please pay attention to the block between the line no 152 and 179. This is the heart of the problem.

Thankyou very much for the reply. Can you please explain a little more how can I use count in this perticular case.

Please pay attention to the block between the line no 152 and 179. This is the heart of the problem.

Use:

$query = mysql_query("SELECT COUNT(*) FROM `search`", $connection);

instead of:

$query = mysql_query("SELECT * FROM `search`", $connection);
mysql
$count = mysql_num_rows($query);

(Search is my Table, you change it to yours)

Not time to wade your stuff either but any DBA worth his salt will tell you that
"SELECT * from ..."
will always be more costly (resource-wise) than "
SELECT whatever_column_1,whatever_column_2,whatever_column_3... from..."

My advice to you is to rewrite your queries and specify each column name. Hopefully, you have indexed columns.

My 2 cents.

Al.

Can i have a DB with some data, would like to test on localhost!

I had a code written long time ago which was working fine for small records but as the number of records begin to increase it completely failed. I am attaching the file with comments which will help you understand the problem.

For fewer records you can see it working at http://dssln.info/search_logistic_providers.php
For large records you can see its behaviour at http://216.14.120.171/search_logistic_providers.php

Basically this code depends on three tables
1) transport
2) services
3) servinv

transport table contains detail of different transport companies and has about 20,000 records
services table stores different services which can be provided by transporters and has 235 records
servinv maps each transporter to each service and tells whether a specific transporter offer this service
or not and it has about 4,233,841 records.

My code completely fails to handle such a large number of records so I need some one to help me optimize
my queries to make it work

You should use mysql_real_escape_string() to quote any user supplied data.

The main rule to follow is to never select the whole table.

If transport has 20, 000 entries and you do:

$p="SELECT memid FROM transport";

Then you are retrieving 20, 000 results.

Then you use this in a query as such:

SELECT DISTINCT transport.memid ... 
WHERE transport.memid IN(SELECT memid FROM transport) ...

This makes each row in the result of the first query, compare with each result from the subquery.

The query is the same as:

SELECT DISTINCT transport.memid ...
WHERE transport.memid > 0;

Which makes sure only rows with a valid memid is returned.

Here is the docs on JOINs in MySQL and how to optimize them.

http://dev.mysql.com/doc/refman/5.0/en/join.html
http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html

Thankyou very much to all of you for your response. Actually the reason of all the bottleneck in the execution of the script was incorrect indexing. Actually I had not applied indexing properly on the table though their were many other code optimization related issues also which I come to know from you people. I reindexed three columns in the servinv table and everything started working. My code still has space for optimization for better performance which I will do surely however the bottleneck is no more there. Please feel free to mention any other bad practice which I have been using as digital-ether has mentioned. you can see how much better it is now after indexing at http://dssln.com/search_logistic_providers.php

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.