Re: SELECT Performance and INDEXing

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@raincross-tech.com> To: <php-db@lists.php.net> Sent: Thursday, August 18, 2005 9:49 AM Subject: Re: [PHP-DB] 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@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. -- --