Large tables and query takes long time

johnie

Hi.
I have a query which when it's first run takes a long time, more than 60 sec. On subsequent runs its very fast (< 1 sec) since the tables are already in memory. However after some time the same problem happens.

The problem is the 2 tables i use are quite large, 30mb / >1100K rows and 50mb / > 140K rows, and i can't do anything about it. The queries are also optimized.

SELECT count(i.ProductNumber) FROM tbl2 as i, tbl1 as c WHERE i.ProductNumber = c.ProductNumber AND c.CategoryNumber = $cat

This is used to count the rows so that i can paginate.

SELECT ii.a, ii.b, ii.c, ii.d, ii.e FROM db2 as ii, db1 as cr WHERE (ii.e = cr.e) AND (cr.a = $cat) ORDER BY ii.a DESC, ii.b ASC LIMIT $offset, $results_per_page

This is used to get the results.

I can't use the same query because of the LIMIT in the 2nd query.

Any suggestions please?

Thanks,
John

Frederick

Honestly, I don't think you will find a faster way of running the script because of the amount of records.

I see you have your query to count the rows which runs first and must complete before running the second query which will take longer because it must search for specific data.

johnie

Thanks Frederick.
I've thought some ways to speed up the process.

One way is to delete some data from the second table (50mb) which i can calculate otherwise. This way the table's size drops to half. I haven't tried it yet.

My new 🙂 question is the query speed depends more on the number of rows or on the size of the table?

Thanks,
John

Frederick

The number of rows. Once you specify the field to look at, it needs to evaluate all data in each row of that field to find a match. Seeking ID numbers is the fastest and seeking strings or within strings are the slowest.

Table size has no bearing once to specify the field to look at. But on the other hand, the more fields you have in your table, it will take time to populate your page after it finds all matches to display the results. If there are not much records to display, population will be fast.