MySQL Query Help - Multiple queries to same table

Question

I have a table I need to perform a horrible query on (not my db design).

The table is simple:

`id` bigint(255) NOT NULL AUTO_INCREMENT,
`poster` varchar(30) NOT NULL,
`chattext` varchar(255) NOT NULL,
`timeposted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`type` int(3) NOT NULL DEFAULT '0',
`towho` varchar(30) DEFAULT NULL

My current query does what I want, but at a horrible cost to the server. I'm running near max CPU with only a handful of users. Sadly, every 5 seconds they do this pull.

Current query:

SELECT c.poster, c.chattext, c.type, c.towho, c.timeposted, u.utype, u.locz
    FROM chat c
    LEFT JOIN users u ON c.poster=u.name
    WHERE c.type!=4
UNION (
    SELECT c.poster, c.chattext, c.type, c.towho, c.timeposted, u.utype, u.locz
        FROM chat c
        LEFT JOIN users u ON c.poster=u.name
        WHERE c.type=4  AND c.towho='USERNAME')
UNION (
    SELECT c.poster, c.chattext, c.type, c.towho, c.timeposted, u.utype, u.locz
    FROM chat c
    LEFT JOIN users u ON c.poster=u.name
    WHERE c.type=4 AND c.poster='USERNAME')
ORDER BY timeposted DESC LIMIT 0, 25"

This gets performed every 5 seconds by all users online. As you can see, it quickly becomes a resource hog.

I'm used to MSSQL so I should be able to grasp the concepts for MySql, and syntax hasn't been too different. This query was given to me without the user tables added, so I think my mindset is stuck in making this work over finding a better way.

I think I'm doing this the wrong/complicated way. So any assistance in improving performance is appreciated.

Galz · Accepted Answer · 2011-04-20 07:01:19Z

3

I think this simpler query is equivalent to yours. isn't it?

SELECT c.poster, c.chattext, c.type, c.towho, c.timeposted, u.utype, u.locz
    FROM chat c
    LEFT JOIN users u ON c.poster=u.name
    WHERE c.type!=4
          OR
          (c.type=4 AND c.towho='USERNAME')
          OR
          (c.type=4 AND c.poster='USERNAME')
ORDER BY timeposted DESC LIMIT 0, 25

answered Apr 20, 2011 at 7:01

Galz

6,8525 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JClaspill Over a year ago

See, I knew I was making it overcomplicated by sticking to the original query design. Can't believe I missed this, thanks so much Galz.

Collectives™ on Stack Overflow

MySQL Query Help - Multiple queries to same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related