speeding up a REALLY slow querey?

[LRW]

I have a querey formed with a few joins and a lot of fields, and no
kidding, it takes up to 2 minutes for the PHP page to poulate with all
the results (usually around 300).

Now, we have a pretty good server: dual Pentium 4 CPUs and a gig of
RAM. And all other queries seem to fly.
So there must be something about this querey that must be horribly
inefficient and badly written.

Can anyone give me any tips on how I can better this?

Thanks!
Liam

$sql = "SELECT orders.companyid,orders.ordernum,orders.address AS
o_address,orders.city AS o_city,orders.state AS o_state,orders.zipcode
AS o_zipcode,orders.firstname AS o_firstname,orders.lastname AS
o_lastname,".
"tbl_ih_accounts.company,tbl_ih_accounts.addre ss AS
ih_address,tbl_ih_accounts.city AS ih_city,tbl_ih_accounts.state AS
ih_state,tbl_ih_accounts.zipcode AS
ih_zipcode,tbl_ih_accounts.firstname AS
ih_firstname,tbl_ih_accounts.lastname AS ih_lastname,".
"accounts.address AS a_address,accounts.city AS a_city,accounts.state
AS a_state,accounts.zipcode AS a_zipcode,accounts.firstname AS
a_firstname,accounts.lastname AS a_lastname, ".
"orderdate, sp_local, inhouse ".
"FROM orderstatus ".
"LEFT JOIN orders ON (orderstatus.ordernum = orders.ordernum) ".
"LEFT JOIN tbl_ih_accounts ON (tbl_ih_accounts.id = ihid) ".
"LEFT JOIN accounts ON (accounts.username = accountid) ".
"WHERE submitted <> 'yes' ORDER BY orders.ordernum ASC";

[Aggro]

LRW wrote:

Can anyone give me any tips on how I can better this?

Have you tried adding indexes to columns that are used in the search?

[Mike Chirico]

"Aggro" <sp**********@yahoo.com> wrote in message
news:_V***************@read3.inet.fi...

LRW wrote:

Can anyone give me any tips on how I can better this?

Have you tried adding indexes to columns that are used in the search?

Interesting note in the MySQL documentation which seems to say:

Even if indexes are used, they can be improved upon by making sure the
columns are all declared the same size. Especially with the older ISAM
tables --indexes may be skipped altogether unless the length of the declared
columns is equal.

Reference:
http://dev.mysql.com/doc/mysql/en/EXPLAIN.html

Depending on the table type, I think it also helps to run the analyze
command on the tables:

mysql> analyze table <tablename>;
Regards,

Mike Chirico

[David L]

if the underlying tables are big, make sure to do ANALYZE on them. Made
a big difference for me.

LRW wrote:

I have a querey formed with a few joins and a lot of fields, and no
kidding, it takes up to 2 minutes for the PHP page to poulate with all
the results (usually around 300).

Now, we have a pretty good server: dual Pentium 4 CPUs and a gig of
RAM. And all other queries seem to fly.
So there must be something about this querey that must be horribly
inefficient and badly written.

Can anyone give me any tips on how I can better this?

Thanks!
Liam

$sql = "SELECT orders.companyid,orders.ordernum,orders.address AS
o_address,orders.city AS o_city,orders.state AS o_state,orders.zipcode
AS o_zipcode,orders.firstname AS o_firstname,orders.lastname AS
o_lastname,".
"tbl_ih_accounts.company,tbl_ih_accounts.addre ss AS
ih_address,tbl_ih_accounts.city AS ih_city,tbl_ih_accounts.state AS
ih_state,tbl_ih_accounts.zipcode AS
ih_zipcode,tbl_ih_accounts.firstname AS
ih_firstname,tbl_ih_accounts.lastname AS ih_lastname,".
"accounts.address AS a_address,accounts.city AS a_city,accounts.state
AS a_state,accounts.zipcode AS a_zipcode,accounts.firstname AS
a_firstname,accounts.lastname AS a_lastname, ".
"orderdate, sp_local, inhouse ".
"FROM orderstatus ".
"LEFT JOIN orders ON (orderstatus.ordernum = orders.ordernum) ".
"LEFT JOIN tbl_ih_accounts ON (tbl_ih_accounts.id = ihid) ".
"LEFT JOIN accounts ON (accounts.username = accountid) ".
"WHERE submitted <> 'yes' ORDER BY orders.ordernum ASC";

[Siemel Naran]

"LRW" <de**@celticbear.com> wrote in message

I have a querey formed with a few joins and a lot of fields, and no
kidding, it takes up to 2 minutes for the PHP page to poulate with all
the results (usually around 300). $sql = "SELECT orders.companyid,orders.ordernum,orders.address AS
o_address,orders.city AS o_city,orders.state AS o_state,orders.zipcode
AS o_zipcode,orders.firstname AS o_firstname,orders.lastname AS
o_lastname,".
"tbl_ih_accounts.company,tbl_ih_accounts.addre ss AS
ih_address,tbl_ih_accounts.city AS ih_city,tbl_ih_accounts.state AS
ih_state,tbl_ih_accounts.zipcode AS
ih_zipcode,tbl_ih_accounts.firstname AS
ih_firstname,tbl_ih_accounts.lastname AS ih_lastname,".
"accounts.address AS a_address,accounts.city AS a_city,accounts.state
AS a_state,accounts.zipcode AS a_zipcode,accounts.firstname AS
a_firstname,accounts.lastname AS a_lastname, ".
"orderdate, sp_local, inhouse ".
"FROM orderstatus ".
"LEFT JOIN orders ON (orderstatus.ordernum = orders.ordernum) ".
"LEFT JOIN tbl_ih_accounts ON (tbl_ih_accounts.id = ihid) ".
"LEFT JOIN accounts ON (accounts.username = accountid) ".
"WHERE submitted <> 'yes' ORDER BY orders.ordernum ASC";

Is column submitted from the orders table? The query may be slow because
very few rows meet the condition, and the system probably does a full table
scan because of all records in the table.

What are the possible values of submitted? If just true and false, or 'yes'
and 'no', or 'Y' or 'N', or 1 or 0, then try

create index specialname on orders ( submitted, ordernum );

select ...
from ...
where orders.submitted = 'no'
order by orders.submitted, orders.ordernum;

[LRW]

"Mike Chirico" <mc******@comcast.net> wrote in message news:<DO********************@comcast.com>...

"Aggro" <sp**********@yahoo.com> wrote in message
news:_V***************@read3.inet.fi...

> Have you tried adding indexes to columns that are used in the

search?

Depending on the table type, I think it also helps to run the analyze

command on the tables:

mysql> analyze table <tablename>;

HUZAH!
I ran "analyze" on each table, and I got back a message each time
saying "Msg_text: OK" on each one.
I guess that's good? Does analyze actually DO anything to the table or
does it just check it for problems?
Anyway, after I did that an dreloaded the querey, it still took 2
minutes.

So I indexed the columns on each table that are used in the joins, and
the next reload took only 15 seconds!! Woohoo!!

Now, I'm assuming that when you index, the benefits of indexing is
sort of like a bell-curve. The more you index the better performance,
but then you can index too much and performance decreases again and
you're simply defeating the purposes--if I understand indexing right,
yes?

So, what's an optimal amount to index? Is it just the "key" field of
each table? Any overlapping column that's part of a join? Any other
tips?

Thanks all!!
Liam

[Aggro]

LRW wrote:

So, what's an optimal amount to index? Is it just the "key" field of
each table? Any overlapping column that's part of a join? Any other
tips?

Optimal amount is to index those fields that need indexing. If you are
unsure which of your fields need it. You can add-try-remove-try again to
find out what indexes are most needed to speed up queries. It might slow
down queries at some point if you have very much indexes, but I assume
that would take quite a lot indexes.

But remember that each time you add a index to your table, the speed to
insert rows into the table slows down a little. You don't see the
difference, unless you are inserting thousands of rows at the same time
to the table. So normally this doesn't matter.