Page 1 of 1

perfomance related question

Posted: Sat Mar 18, 2006 2:37 pm
by newmember
there are 2 tables: image and gallery

here is a query:

Code: Select all

$qr="EXPLAIN SELECT image_id FROM image WHERE image_galleryId='".$id."' LIMIT 31,60";
it selects all images from specific gallery

EXPLAIN output:
id: 1
select_type: SIMPLE
table: image
type: ref
possible_keys: image_galleryId
key: image_galleryId
key_len: 4
ref: const
rows: 504
extra: Using where; Using index

You can see that despite LIMIT clause, db says that it needs to examine 8O 504 rows to complete the query...

I tried to see what happens as i add more rows to image table(for the same gallery)... i measured execution times by using this basic setup:

Code: Select all

$time_start=microtime();
$res=mysql_query($qr);
$time_end=microtime();
echo($time_end-$time_start);
the results were that as more rows were added to image table(to specific gallery) execution time increase... 8O
starting from 0.00xxxx sec. for few rows toward 0.1xxxx sec for ~500 rows...(actualy times for more complex query)
Apparently this happens because database kind of "ignores" LIMIT clause... it always scans all the rows :?
I was execting pretty constant execution time no matter how many rows do i have.

Am I doing something wrong with query?

Posted: Sat Mar 18, 2006 3:07 pm
by feyd
The LIMIT is applied after the rest of the query is performed. This allows ORDER BY, GROUP BY, and other clauses to complete what they are supposed to do.

Posted: Sat Mar 18, 2006 3:11 pm
by newmember
ok, LIMIT clause is not really the problem

the problem is the query:

$qr="EXPLAIN SELECT image_id FROM image WHERE image_galleryId='".$id."' LIMIT 10,40";

if some gallery has 1000 images then when executing this query database will find all images where

Code: Select all

image_galleryId='".$id."'
and that equals 1000 rows...if there were 10000 rows in that gallery then all of it would be scanned....
and we get dependance on number of rows ...

so, is there a way/technics to overcome this?
(if i could somehow narrow the search range with additional index maybe :? )

Posted: Sun Mar 19, 2006 9:14 am
by newmember
out of curiousity i went and downloaded phpbb forum board to see how they handle similar situation....
where forum can have many threads...

looking at the code i found out that they simply do what i did( i removed unrelevant pieces ):
$sql = "SELECT t.* FROM " . TOPICS_TABLE . " t WHERE t.forum_id = $forum_id"
but this doesn't scale well with large number of topics (under specific forum)....
:?

Posted: Sun Mar 19, 2006 9:39 am
by feyd
it scales fine, linear.

Posted: Sun Mar 19, 2006 9:49 am
by newmember
linear also means increase though slow one...

it would be nice to have something that would retrieve any page within (almost) constant time regarless number of rows

it is possible... using intervals (like in b++ trees) but adds a lot of complexity

Posted: Mon Mar 20, 2006 1:03 am
by AKA Panama Jack
newmember wrote:ok, LIMIT clause is not really the problem

the problem is the query:

$qr="EXPLAIN SELECT image_id FROM image WHERE image_galleryId='".$id."' LIMIT 10,40";

if some gallery has 1000 images then when executing this query database will find all images where

Code: Select all

image_galleryId='".$id."'
and that equals 1000 rows...if there were 10000 rows in that gallery then all of it would be scanned....
and we get dependance on number of rows ...

so, is there a way/technics to overcome this?
(if i could somehow narrow the search range with additional index maybe :? )
Actually that is not true...

Mysql will immediately STOP the table scan as soon as it finds the 40th matching record in your above example. It may have to scan through 50, 700 or all of those 1,000 records to find 40 matching records but it WILL stop as soon as it finds it. The only time Mysql would scan all 1,000 records in the above instance is if there are fewer than 40 matching records or the 40th match is the 1,000th record in that 1,000 record table.

Posted: Mon Mar 20, 2006 2:30 am
by Benjamin
AKA Panama Jack, I think what your saying is true IF there are not certain clauses in the query, such as order by for example.

Posted: Mon Mar 20, 2006 11:20 am
by newmember
AKA Panama Jack, may be what you saying is true for some cases...
but in my situation mysql db DOES scan all rows for which

Code: Select all

image_galleryId='".$id."'

it can be seen simply from adding each time a few hundred rows and measuring the execution time...
(i wrote about this in my post)

also take a closer look at EXPLAIN dump
join type is ref meaning (from manual)
All rows with matching index values are read from this table for each combination of rows from the previous tables.
and rows says 504... meaning(from manual)
The rows column indicates the number of rows MySQL believes it must examine to execute the query.

despite there is LIMIT clause...