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
