mysql optimization

Hi!

I'm having problems optimizing a slow query - I have tried to understand the
output of explain and everything, but I guess I'm missing something. I would
appreciate if someone could help me out...

Update: while writing this question I have (probably) figured out where the
problem lies, but I would still appreciate a comment on how to solve it...

The query should take 5 latest posts from vBulletin:
-----
select
p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text'
from
thread t, post p
where
p.threadid=t.threadid and
t.visible=1 and
t.open=1
order by
p.postid desc limit 5;
-----

Slow queries log tells me this:
# Time: 071127 16:26:41
# Query_time: 4 Lock_time: 0 Rows_sent: 5 Rows_examined: 252099
use mydb;
select p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text' from
thread t, post p where p.threadid=t.threadid and t.visible=1 and t.open=1
order by p.postid desc limit 5;

There lies the problem - 250000 rows examined... It must not be using index.
Let's see what explain says:

------------------------------------------------------------ ------------
id select_type table type possible_keys key key_len
1 SIMPLE t ALL PRIMARY NULL NULL
1 SIMPLE p ref threadid threadid 4
------------------------------------------------------------ ------------
------------------------------------------------------------ ------------
ref rows Extra
NULL 3356 Using where; Using temporary; Using filesort
mydb.t.threadid 3
------------------------------------------------------------ ------------

I would guess the problem lies in the select from table "thread", but we are
selecting by threadid which is primary key on that table.

Problem: MySQL is first performing a join on the table, then ordering, then
limiting results to 5 latest - which is of course very slow. This would be
the solution:
SELECT title
FROM thread
WHERE threadid
IN (
SELECT threadid
FROM post
ORDER BY postid DESC
LIMIT 5
)

But MySQL doesn't support LIMIT inside subqueries, besides, I want to get
only postids from threads that are not hidden (t.visible=1 and t.open=1).

How can I do this?

Thank you for all suggestion, tips, solutions... I'm still struggling with
mysql query optimization...

Anze

Re: mysql optimization

On 6 Dec, 13:13, Anze wrote:
> Hi!
>
> I'm having problems optimizing a slow query - I have tried to understand the
> output of explain and everything, but I guess I'm missing something. I would
> appreciate if someone could help me out...
>
> Update: while writing this question I have (probably) figured out where the
> problem lies, but I would still appreciate a comment on how to solve it...
>
> The query should take 5 latest posts from vBulletin:
> -----
> select
> p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text'
> from
> thread t, post p
> where
> p.threadid=t.threadid and
> t.visible=1 and
> t.open=1
> order by
> p.postid desc limit 5;
> -----
>
> Slow queries log tells me this:
> # Time: 071127 16:26:41
> # Query_time: 4 Lock_time: 0 Rows_sent: 5 Rows_examined: 252099
> use mydb;
> select p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text' from
> thread t, post p where p.threadid=t.threadid and t.visible=1 and t.open=1
> order by p.postid desc limit 5;
>
> There lies the problem - 250000 rows examined... It must not be using index.
> Let's see what explain says:
>
> ------------------------------------------------------------ ------------
> id select_type table type possible_keys key key_len
> 1 SIMPLE t ALL PRIMARY NULL NULL
> 1 SIMPLE p ref threadid threadid 4
> ------------------------------------------------------------ ------------
> ------------------------------------------------------------ ------------
> ref rows Extra
> NULL 3356 Using where; Using temporary; Using filesort
> mydb.t.threadid 3
> ------------------------------------------------------------ ------------
>
> I would guess the problem lies in the select from table "thread", but we are
> selecting by threadid which is primary key on that table.
>
> Problem: MySQL is first performing a join on the table, then ordering, then
> limiting results to 5 latest - which is of course very slow. This would be
> the solution:
> SELECT title
> FROM thread
> WHERE threadid
> IN (
> SELECT threadid
> FROM post
> ORDER BY postid DESC
> LIMIT 5
> )
>
> But MySQL doesn't support LIMIT inside subqueries, besides, I want to get
> only postids from threads that are not hidden (t.visible=1 and t.open=1).
>
> How can I do this?
>
> Thank you for all suggestion, tips, solutions... I'm still struggling with
> mysql query optimization...
>
> Anze

Since there is no PHP in this, I would suggest that you would most
likely get more response in comp.databases.mysql, which also tends to
be a far busier group.

Re: mysql optimization

Anze wrote in
news:OzS5j.2460$HS3.78862@news.siol.net:

> Hi!
>
> I'm having problems optimizing a slow query - I have tried to
> understand the output of explain and everything, but I guess I'm
> missing something. I would appreciate if someone could help me out...
>
> Update: while writing this question I have (probably) figured out
> where the problem lies, but I would still appreciate a comment on how
> to solve it...
>
> The query should take 5 latest posts from vBulletin:
> -----
> select
> p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text'
> from
> thread t, post p
> where
> p.threadid=t.threadid and
> t.visible=1 and
> t.open=1
> order by
> p.postid desc limit 5;
> -----



> How can I do this?
>
> Thank you for all suggestion, tips, solutions... I'm still struggling
> with mysql query optimization...
>
> Anze
>
>


You definetly should have an index on t.visible and t.open, thats for
sure. My guess is that you don't. My rule-of-thumb (mine, not "the")
is to put an INDEX on every column that will be involved in a JOIN or
WHERE clause:

Anyways, put INDEXes on those columns and use a nice proper JOIN:


SELECT
p.postid, t.title 'ttitle', p.title 'ptitle', p.pagetext 'text'
FROM
thread t
JOIN post p ON t.threadid=p.threadid
WHERE t.visible=1 AND t.open=1
ORDER BY
p.potid DESC
LIMIT 5

Re: mysql optimization

> Since there is no PHP in this, I would suggest that you would most
> likely get more response in comp.databases.mysql, which also tends to
> be a far busier group.

Thank you for pointing me in the right newsgroup... It looks like my
provider only serves some of the comp.* groups... :(

Anze

Re: mysql optimization

Post removed (X-No-Archive: yes)

Re: mysql optimization

Hi!

Good Man wrote:
> ...
> I know you don't want to change the database, but note that you're asking
> for this:
>
> "WHERE t.visible=1 and t.open=1"
>
> So, unless you have an index on those columns, the MySQL optimizer (or any
> other optimizer) has no choice but to examine every single row to check
> for those conditions.

Sorry for late response, I didn't see the answers till now...

Actually i solved it by fetching a bit more records and discarding the ones
that are not visible / open. In theory it could happen that there are less
records fetched because of that, but it doesn't matter in this case - this
is "last X posts in forum" and the posts are being added every minute (they
are almost always visible), besides, it is very important that it's fast
but it's not that important that it's accurate...

So thanks again, I have solved it without the index, and I prefer it that
way... (The forum is being supported by someone else so I don't want to
mess things there :)

Thanks,

Anze