Hello,

Just a little question. I'm creating an online catalog and there is a category that returns me about 140.000 entries.

It is a relational query, I've already created indexes, but it takes a long time to execute the sentence.

Is there a way to increase the speed of the query? It currently takes about 40 seconds!

Here is the one I use:

SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD1, TABLE2.FIELD1 FROM TABLE1, TABLE2 WHERE TABLE1.FIELD5='%s' AND TABLE2.FIELDINDEX=TABLE1.FILEINDEX GROUP BY TABLE1.FIELD1 ASC",$varQUERY);
$query_limit_rsQUERY = sprintf("%s LIMIT %d, %d", $query_rsQUERY, $startRow_rsQUERY, $maxRows_rsQUERY);

Thanks a lot!

NArcís

The result of your query is a cross join. This is many times slower than an inner join. Try this query:

SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD1, TABLE2.FIELD1 FROM TABLE1 INNER JOIN TABLE2 ON TABLE2.FIELDINDEX = TABLE1.FIELDINDEX WHERE TABLE1.FIELD5='%s' GROUP BY TABLE1.FIELD1 ASC;

Hello reneeb,

Thanks a lot for your help, I've changed the query but page loading has just improved about 3 seconds. However that makes browsing the other parts of the catalog faster!

I feel that the problem is that we get too many results, and I would like to know if I could limit it a little bit as querys with about 2.000 results work perfectly. I tried the same query but removing the cros query and it takes the same time.

I was thinking in a kind of mysql option that made large tables run faster, or a way to stop the query when it has more than 5.000 results (as it make not sense having so many results).

Thanks a lot,

Narcís

a limitation of results??

Take this:

SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD1, TABLE2.FIELD1 FROM TABLE1 INNER JOIN TABLE2 ON TABLE2.FIELDINDEX = TABLE1.FIELDINDEX WHERE TABLE1.FIELD5='%s' GROUP BY TABLE1.FIELD1 ASC LIMIT 2000;



If you have an index on table1.field5 it would run faster...