perfomance related question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

perfomance related question

Post 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?
Last edited by newmember on Sat Mar 18, 2006 3:46 pm, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post 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 :? )
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post 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)....
:?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it scales fine, linear.
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post 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
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
User avatar
newmember
Forum Contributor
Posts: 252
Joined: Fri Apr 02, 2004 12:36 pm

Post 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...
Post Reply