There is an option long_query_time that you can use to log query that
take longer then a preset time, if that's of any help.
cheers,
roel
On Sat, Apr 07, 2001 at 02:05:13PM +0100, Ewan Birney wrote:
>
>
> We are very happy users of MySQL, which we run a nice big genome database
> on. We have a web front end to the database (www.ensembl.org if anyone is
> interested) and we are looking at getting our pages to come back faster.
>
>
> I have been moving things out of Perl into C for the heavier lifting
> between the database and the actual html/gif generation. Although there is
> alot more milage in this, we may soon be coming up to a mysql side limit.
>
>
> The main queries join two tables, both with indexes, looking like:
>
>
>
> sprintf(sqlbuffer,
> "SELECT IF
> (sgp.raw_ori=1,(f.seq_start+sgp.chr_start-sgp.raw_start-%d),"
> " (sgp.chr_start+sgp.raw_end-f.seq_end-%d)), "
> " IF
> (sgp.raw_ori=1,(f.seq_end+sgp.chr_start-sgp.raw_start-%d),"
>
> " (sgp.chr_start+sgp.raw_end-f.seq_start-%d)),"
> " IF (sgp.raw_ori=1,f.strand,(-f.strand)),"
> " f.id, f.score, f.analysis,"
> " f.hstart, f.hend, f.hid"
> " FROM %s f,static_golden_path sgp"
> " WHERE sgp.raw_id = f.contig AND sgp.chr_end >= %d AND
> sgp.chr_start <= %d AND sgp.chr_name = '%s'"
> " AND f.score > '%s'",
> start,start,start,start,table,start,end,chr_name,score);
>
>
> The table f can vary depending on the precise query. The main join is
>
> sgp.raw_id = f.contig
>
> with the main restriction being
>
> sgp.chr_end >= X AND sgp.chr_start <= Y
>
> getting an interval of rows on sgp. We have appropiate keys on the tables.
>
> Notice the nice if statements returning stuff, which work very well and
> don't seem to slow things down at all...
>
>
> Cardinality is about 5,000,000 rows for f, with the f.contig being around
> 400,000 unique ids (integers), whereas sgp has about 100,000 rows. The
> final query will return anywhere between 2 and 150 unique f.contig ids
> which get expanded to something like 20 to 5000 rows in the f table.
> The query comes back between 0.1 and 1.5 seconds (depending on the precise
> make up of the data), and this might soon dominate the page refresh rate.
>
> At the moment, the page refresh for larger regions is > 40 seconds, which
> is unacceptable to users, so improving this query would be a big win.
>
> I am interested at the moment about how we go about investigating ways of
> getting this to go faster. At the moment we have a rather crude profiling
> tool for aspects of how fast things comes back inside the HTML generation
> code and of course we can run things from the mysql client to test query
> times.
>
>
> Does anyone have any advice about what we should look into first about
> improving turn-around
>
>
>
> thanks
>
>
>
> ewan
>
>
>
> -----------------------------------------------------------------
> Ewan Birney. Mobile: +44 (0)7970 151230, Work: +44 1223 494420
> <[EMAIL PROTECTED]>.
> -----------------------------------------------------------------
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
