slow mysql "sending data" stage between mysql and php

[vladimir_cz]

Hi!

I have problem:
I have IPB forum installed. After search in IPB (search takes about 3-4
seconds for post table about 300 000 records, ~250mb data) mysql shows "Sending
Data"
status and takes about 3-5 minutes.

I tested with PHP 5.2, PHP 4.4, PHP 4.2.3 - with same results.
I switched from MySQL 4.2.18 to 4.2.21 and 4.2.22 with same result.

I tested with apache module, fastCGI with NGINX web server - same
results.

I tested mysql connections with socket and network connection.

May be I missed some variables from php.ini?
May be I missed some variables from my.cnf?
Main question - how I can speedup code for accept "Sending data" stage
between PHP and MySQL more fast?

[stuart]

To be honest, I do not know what the '"Sending Data" stage is.... but here are a couple of things to check.
There are various setting in mysql which may need tweeking... some of these include: query_cache_size, table_cache, tmp_table_size... and many others.
Also check your indexes.

But again, this is only a guess as I have no idea what 'Sending Data' is really doing.

[vladimir_cz]

just for clarification - I installed latest mySQL 5 and check it without PHP (with console client). same results.
main problem - there is "IN" with a lot of ids inside.
I have no idea how I can make it faster (only by two steps with creation temporary table)

[Xnuiem]

using a bunch of OR statements is actually faster than IN.

Also, is the field indexed properly?

What does the SQL statement look like?

[vladimir_cz]

SELECT distinct(p.pid), p.queued, t.approved, t.forum_id
FROM fgrposts p LEFT JOIN fgrtopics t on (t.tid=p.topic_id)
WHERE
t.forum_id IN (78,73,74,75,76,63,64,65,57,36,45,59,46,32,41,33,3 1,34,35,40,48,60,68,49,37,38,39,47,50,42,67,43,66)
AND p.queued=0 AND ( LOWER(p.post) LIKE '%girl%' )


there is explain for select:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t ALL PRIMARY,forum_id,last_post NULL NULL NULL 13668 Using where; Using temporary
1 SIMPLE p ref 5last,topic_id,queued 5last 4 gdv_ru.t.tid 24 Using where