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
