Re: SELECT Performance and INDEXing

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



No change on either variation. I also tried WHERE field2 = 1 OR field1 < 3 as well as omitting the ORDER BY clause.

Regarding INDEX: http://dev.mysql.com/doc/mysql/en/mysql-indexes.html " If a multiple-column index exists on col1 and col2, the appropriate rows can be fetched directly. If separate single-column indexes exist on col1 and col2, the optimizer tries to find the most restrictive index by deciding which index finds fewer rows and using that index to fetch the rows."

From the docs, I would have thought that an INDEX on field2 is all I needed,
but perhaps I need an index on (field1, field2) since they are both in the where clause?

I didn't add the INDEX on field1. Would you explain a little more about them? field1 is already a PRIMARY key and listed in the Indexes box in phpmyadmin as Type:PRIMARY. I did create an INDEX on field2 and it is listed in the Indexes box in phpmyadmin as Type:INDEX. But apparently they are both "indexes". Also, do you mean create a separate index for field1 or some kind of composite index {i.e. multiple-column index} on (field1, field2)? (field2, field3) would actually be a unique composite key, so I was wondering if some type of composite index on (field2, field3) would be better. Then again, any combination with field1 would also be unique.



----- Original Message ----- From: "Micah Stevens" <micah@xxxxxxxxxxxxxxxxxx>
To: <php-db@xxxxxxxxxxxxx>
Sent: Thursday, August 18, 2005 12:33 PM
Subject: Re:  SELECT Performance and INDEXing


Okay, well, I guess that EXPLAINS it.. (bad joke.. sorry.)

Not being a huge expert on the inner working on MySQL, I'm at a loss to
explain why this is happening exactly, but it's clear that MySQL is choosing
to do a filesort over 6400 rows with the first statement. This is probably
due to it's inability to index based on your where statement.

The other statements can index out much smaller result sets, then order then
and deliver (8 & 10 respectively) therefore accomplishing much faster.

What I would do at this point, is play with reordering, or rewriting the SQL
for the first statement to see if that makes a difference. I know if JOIN
statements, the order of the WHERE condition can make a huge difference.
Here's an example that assumes Field1 will never be less than 1. This may be
an incorrect assumption, but it illustrates my point:


SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 < 3
OR field2 = 1
ORDER BY field1 ASC

-or-

SELECT field1, field2, andUpToField10
FROM theTable
WHERE field1 = 1
OR field1 = 2
OR field2 = 1
ORDER BY field1 ASC
(this is just subtly different, but I'd be curious if it affects the outcome)

Also, add an index on field1 and field2 if you don't already, as that may
help. (sometimes it doesn't, depending on data type)

I hope that helps,
-Micah

On Thursday 18 August 2005 4:30 pm, Dwight Altman wrote:
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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux