Results 1 to 4 of 4

Thread: slow query

  1. #1
    Join Date
    Oct 2003
    Posts
    5

    Unanswered: slow query

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    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;
    board.perl-community.de - The German Perl-Community

  3. #3
    Join Date
    Oct 2003
    Posts
    5
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Germany
    Posts
    167
    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...
    board.perl-community.de - The German Perl-Community

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •