Re: SELECT Performance and INDEXing

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@multicam.com> > To: <php-db@lists.php.net> > Sent: Wednesday, August 10, 2005 4:23 PM > Subject: [PHP-DB] 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. > > -- >