CODEWITHSUNDEEP
phpmysql
user1551419
user1551419

Reputation: 5

How can I optimize this query to get result from it faster?

I have this query, that I execute a lot of times. Can you please suggest how to optimize this? Indexes are already applied to the table.

SELECT
    DISTINCT b.appearance_id,
    b.photo_album_id,
    b.eventcmmnt_id,
    b.id,
    b.mem_id,
    b.subj,
    b.body,
    b.date,
    b.parentid,
    b.from_id,
    b.visible_to,
    b.image_link,
    b.post_via,
    b.youtubeLink,
    b.link_image,
    b.link_url,
    b.auto_genrate_text
FROM
    bulletin b
INNER JOIN
    network n
ON
    ( n.mem_id = b.mem_id )
WHERE
    b.parentid = '0' AND 
    '$userid' IN (n.frd_id, b.mem_id))
GROUP BY
    b.id
ORDER BY
    b.date DESC
LIMIT
    0,20

Upvotes: 0

Views: 75

Answers (1)

Fluffeh
Fluffeh

Reputation: 33542

Are network.frd_id, network.mem_id and bulletin.mem_id all indexed? If they are not, you will be in chaos if there are some larger volumes in the tables.

Also, and ('$userid' IN (n.frd_id, b.mem_id)) is terrible in terms of cost. If possible you should always stick to =, != and the like. Is it possible to replace this line with and ('$userid' = n.frd_id or '$userid = b.mem_id) ?

Lastly, if you are looking at some serious volumes in terms of records, re-ordering the table may be a big win for you. I run some heavish databases at work which contain large volumes of data identified by a key value. Normally, I want to aggregate these values up to some level. I have found that running an occasional alter table someTableName order by col1, col2; to group the records into the chunks that are likely to be aggregated or sifted through together makes a drastic improvement.

Upvotes: 1

Related Questions