Optimizing Query

------=_Part_158485_9886308.1185332894718
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi list,

I Have a problem with my query that fetch about 22.000 records from
database, the query is like this :

SELECT SQL_CALC_FOUND_ROWS DISTINCT b.biblio_id, b.title,
a.author_name FROMbiblio AS b LEFT
JOIN biblio_author AS ba ON b.biblio_id=ba.biblio_id LEFT JOIN mst_author AS
a ON ba.author_id=a.author_id WHERE b.biblio_id IS NOT NULL GROUP BY
b.biblio_id ORDER BY b.input_date DESC LIMIT 0, 10

it took about 6 seconds to complete the query in localhost. I already make
an Indexes on each table, but still the performance is slow.
Anyone have an idea how to optimize the query so result is faster?

Thx

------=_Part_158485_9886308.1185332894718--

Re: Optimizing Query

Arie Nugraha wrote:
> Hi list,
>
> I Have a problem with my query that fetch about 22.000 records from
> database, the query is like this :
>
> SELECT SQL_CALC_FOUND_ROWS DISTINCT b.biblio_id, b.title,
> a.author_name FROMbiblio AS b LEFT
> JOIN biblio_author AS ba ON b.biblio_id=ba.biblio_id LEFT JOIN
> mst_author AS
> a ON ba.author_id=a.author_id WHERE b.biblio_id IS NOT NULL GROUP BY
> b.biblio_id ORDER BY b.input_date DESC LIMIT 0, 10
>
> it took about 6 seconds to complete the query in localhost. I already make
> an Indexes on each table, but still the performance is slow.
> Anyone have an idea how to optimize the query so result is faster?

Why are you doing a distinct AND a group by? You don't need both, just
use the DISTINCT.

I'm guessing biblio(biblio_id) is a primary key - you don't need:

b.biblio_id IS NOT NULL


What indexes do you have? You need ones on each field in the joins:

biblio(biblio_id)
biblio_author(biblio_id, author_id)
mst_author(author_id)

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Optimizing Query

Arie Nugraha napsal(a):
> Hi list,
>
> I Have a problem with my query that fetch about 22.000 records from
> database, the query is like this :
>
> SELECT SQL_CALC_FOUND_ROWS DISTINCT b.biblio_id, b.title,
> a.author_name FROMbiblio AS b LEFT
> JOIN biblio_author AS ba ON b.biblio_id=ba.biblio_id LEFT JOIN
> mst_author AS
> a ON ba.author_id=a.author_id WHERE b.biblio_id IS NOT NULL GROUP BY
> b.biblio_id ORDER BY b.input_date DESC LIMIT 0, 10
>
> it took about 6 seconds to complete the query in localhost. I already
> make
> an Indexes on each table, but still the performance is slow.
> Anyone have an idea how to optimize the query so result is faster?
>
> Thx
>
optimalisation requests should be supplied with result of explain and
table structure. Without that it is just guessing what may went wrong.

OKi98

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php