|
Home > Archive > MySQL ODBC Connector > February 2006 > Help with Query Optimization
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Help with Query Optimization
|
|
| Kishore Jalleda 2006-02-28, 8:28 pm |
| ------ =_Part_18704_1303206
4.1141153115491
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Hi All,
We use Vbulletin for our message boards, and I have a query which
takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid =3D deletionlog.
primaryid
AND TYPE =3D 'thread' )
WHERE forumid =3D98
AND sticky =3D0
AND visible =3D1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15
There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
Select gives me this......
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
thread
index
*NULL*
lastpost
4
*NULL*
112783
Using where
1
SIMPLE
deletionlog
eq_ref
PRIMARY
PRIMARY
5
foxbox16.thread.threadid,const
1
Using where; Using index; Not exists
I am no SQL guru so can somebody kindly help me to optimize this query so
that it could run faster , as during peak times this slows the DB down a
lot..........
Kishore Jalleda
------ =_Part_18704_1303206
4.1141153115491--
| |
| Peter Brawley 2006-02-28, 8:28 pm |
| --=======AVGMAIL-4404AC3F0367=======
Content-Type: multipart/alternative; boundary=------------ 00080204070001030907
0608
-------------- 00080204070001030907
0608
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Kishore,
> We use Vbulletin for our message boards, and I have a query which
>takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
>having around 130,000 rows, it is as follows
>SELECT threadid
>FROM thread
>LEFT JOIN deletionlog
>ON ( thread.threadid = deletionlog.primaryid AND TYPE = 'thread' )
>WHERE forumid = 98
> AND sticky = 0
> AND visible = 1
> AND deletionlog.primaryid IS NULL
>ORDER BY sticky DESC , lastpost DESC
>LIMIT 0 , 15
Your formatting of EXPLAIN output is a bit hard to read, but did you try
indexes on
(sticky,lastpost) and (forumid,sticky,visi
ble)? And depending on how big
deletionlog is,
you might move ...
AND deletionlog.primaryid IS NULL
from the WHERE clause to the ON clause, to reduce the number of rows the
query engine
has to write to its temp table.
PB
-----
Kishore Jalleda wrote:
> Hi All,
> We use Vbulletin for our message boards, and I have a query which
> takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
> having around 130,000 rows, it is as follows
>
> SELECT threadid
> FROM thread AS thread
> LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid = deletionlog.
> primaryid
> AND TYPE = 'thread' )
> WHERE forumid =98
> AND sticky =0
> AND visible =1
> AND deletionlog.primaryid IS NULL
> ORDER BY sticky DESC , lastpost DESC
> LIMIT 0 , 15
>
> There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
> Select gives me this......
>
> id
> select_type
> table
> type
> possible_keys
> key
> key_len
> ref
> rows
> Extra
> 1
> SIMPLE
> thread
> index
> *NULL*
> lastpost
> 4
> *NULL*
> 112783
> Using where
> 1
> SIMPLE
> deletionlog
> eq_ref
> PRIMARY
> PRIMARY
> 5
> foxbox16.thread.threadid,const
> 1
> Using where; Using index; Not exists
>
>
> I am no SQL guru so can somebody kindly help me to optimize this query so
> that it could run faster , as during peak times this slows the DB down a
> lot..........
>
> Kishore Jalleda
>
>
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
>
-------------- 00080204070001030907
0608
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Kishore,<br>
<br>
> We use Vbulletin for our message boards, and I have a
query which<br>
>takes approximately 1.2 - 1.5 seconds to execute, on a table
('thread')<br>
>having around 130,000 rows, it is as follows<br>
<br>
>SELECT threadid<br>
>FROM thread <br>
>LEFT JOIN deletionlog<br>
>ON ( thread.threadid = deletionlog.primaryid AND TYPE = 'thread' )<br>
>WHERE forumid = 98<br>
> AND sticky = 0<br>
> AND visible = 1<br>
> AND deletionlog.primaryid IS NULL<br>
>ORDER BY sticky DESC , lastpost DESC<br>
>LIMIT 0 , 15<br>
<br>
Your formatting of EXPLAIN output is a bit hard to read, but did you
try indexes on <br>
(sticky,lastpost) and (forumid,sticky,visi
ble)? And depending on how
big deletionlog is, <br>
you might move ...<br>
AND deletionlog.primaryid IS NULL<br>
from the WHERE clause to the ON clause, to reduce the number of rows
the query engine <br>
has to write to its temp table.<br>
<br>
PB<br>
<br>
-----<br>
<br>
Kishore Jalleda wrote:
<blockquote
cite=" mid78aaf671060228105
8u7eb4ff24m62dc20ca7
6e1d956@mail.gmail.com"
type="cite">
<pre wrap="">Hi All,
We use Vbulletin for our message boards, and I have a query which
takes approximately 1.2 - 1.5 seconds to execute, on a table ('thread')
having around 130,000 rows, it is as follows
SELECT threadid
FROM thread AS thread
LEFT JOIN deletionlog AS deletionlog ON ( thread.threadid = deletionlog.
primaryid
AND TYPE = 'thread' )
WHERE forumid =98
AND sticky =0
AND visible =1
AND deletionlog.primaryid IS NULL
ORDER BY sticky DESC , lastpost DESC
LIMIT 0 , 15
There is an index on the field 'lastpost' ( Type: INT(10) ) , also Explain
Select gives me this......
id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1
SIMPLE
thread
index
*NULL*
lastpost
4
*NULL*
112783
Using where
1
SIMPLE
deletionlog
eq_ref
PRIMARY
PRIMARY
5
foxbox16.thread.threadid,const
1
Using where; Using index; Not exists
I am no SQL guru so can somebody kindly help me to optimize this query so
that it could run faster , as during peak times this slows the DB down a
lot..........
Kishore Jalleda
</pre>
<pre wrap="">
<hr size="4" width="90%">
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
</pre>
</blockquote>
</body>
</html>
-------------- 00080204070001030907
0608--
--=======AVGMAIL-4404AC3F0367=======
Content-Type: text/plain; x-avg=cert; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Content-Description: "AVG certification"
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
--=======AVGMAIL-4404AC3F0367=======
Content-Type: text/plain; charset=us-ascii
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
--=======AVGMAIL-4404AC3F0367=======--
|
|
|
|
|