Thanks for your reply. Here are the results of EXPLAIN:
EXPLAIN SELECT field1, field2, andUpToField10
FROM theTable
WHERE field2 = 1
OR field1 = 1
OR field1 = 2
ORDER BY field1 ASC
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE theTable ALL PRIMARY,field2 NULL NULL NULL 6400 Using
where; Using filesort
===========================================================================
Here are the results of EXPLAIN on the separate statements:
EXPLAIN SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 =1
OR field1 =2
ORDER BY field1 ASC
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE theTable range PRIMARY PRIMARY 8 NULL 2 Using where
----------------------------------------------------------------------------------------------------------------------
EXPLAIN SELECT field1, field2, andUpToField10
FROM theTable
WHERE field2 =1
ORDER BY field1 ASC
id select_type table type possible_keys key key_len
ref rows Extra
1 SIMPLE theTable ref field2 field2 9 const 10 Using where; Using
filesort
----- Original Message -----
From: "Micah Stevens" <micah@xxxxxxxxxxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Sent: Thursday, August 18, 2005 9:49 AM
Subject: Re: SELECT Performance and INDEXing
I think no one answered it because it doesn't make a whole lot of sense.
Breaking a condition out into a second SQL statement would force the DB to
rescan the table, so it should take longer rather than shorter. There's
nothing suggesting that it's doing an internal self-join or other
time-consuming function off the bat, which might explain it.
Granted a second query would benefit from caching from the first to some
degree.
Can you run EXPLAIN on the first statement and post the results?
-Micah
On Thursday 18 August 2005 1:02 pm, Dwight Altman wrote:
Hey, this is my first question. So if you could just reply to say it
reached the php-db list, that would be terrific. Of course, answering
the
questions would be awesome as well.
> I meant
> 'Analyze table'
> and
> 'the composite key "field2 field3" would be unique'
>
> ----- Original Message -----
> From: "Dwight Altman" <dwight@xxxxxxxxxxxx>
> To: <php-db@xxxxxxxxxxxxx>
> Sent: Wednesday, August 10, 2005 4:23 PM
> Subject: SELECT Performance and INDEXing
>
>
> I have a MyISAM table holding images with field types bigint(20),
> mediumblob, varchar(255), blob and tinyint(3). The table has grown to
> over
> 800 MB and over 6,000 rows. In the past week, performance has been
> about
> 15-20 seconds to run the following select statement which pulls only 16
> maximum rows:
>
> SQL1) SELECT field1, field2, andUpToField10 FROM theTable WHERE field2
> =
> $aField2Value OR field1 = 1 OR field1 = 2 ORDER BY field1 ASC
>
> Basically I always pull the first 2 images in the table via the primary
> key
> field1 and upto 14 additional images depending on a foreign key field2.
> field2 can have up to 14 repeated/duplicate entries.
>
> My working solution is that I have since split this into 2 select
> statements:
> SQL2) SELECT field1, field2, andUptoField10 FROM theTable WHERE field1
> =
> 1 OR field1 = 2 ORDER BY field1 ASC
> SQL3) SELECT field1, field2, andUptoField10 FROM theTable WHERE field2
> =
> $aField2Value ORDER BY field1 ASC
> and performance is back to "instantaneous" (as far as web
> responsiveness
> is
> concerned).
>
> Can someone explain why SQL1 took so long to run as compared with
> running
> SQL2 and SQL3 ?
>
> Before splitting the statements, I also tried from phpmyadmin (Check
> table,
> Aalyze table, Optimize table) and creating an INDEX on field2, but I
> noticed
> no performance increase. Before explicitly adding an INDEX, the space
> usage
> in phpmyadmin already showed Type:Index using several bytes. The table
> still has the INDEX I explicitly created.
>
> Can someone explain to me INDEXing ? I was thinking of field2 and
> field3
> for an INDEX (since field3 holds a number from 1 - 14 and the composite
> key
> "field1 field2" would be unique), but I seem to be home free already.
> I
> would just like to know "why" performance slowed and then "why" it
> improved
> with my solution.
>
> Regards.
>
> --
--
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
