More Efficient Way To Write MySQL Query?

Question

My site has suddenly started spitting out the following error:

"Incorrect key file for table '/tmp/#sql_645a_1.MYI'; try to repair it"

I remove it, the site works fine.

My server tech support guys suggest I clean up the query and make it more efficient.

Here's the query:

SELECT *, FROM_UNIXTIME(post_time, '%Y-%c-%d %H:%i') as posttime 
FROM communityposts, communitytopics, communityusers 
WHERE communityposts.poster_id=communityusers.user_id 
AND communityposts.topic_id=communitytopics.topic_id 
ORDER BY post_time DESC LIMIT 5

Any help is greatly appreciated. Perhaps can be done with a JOIN?

Many thanks,

Scott

UPDATE: Here's the working query, I still feel it could be optimised though.

SELECT
    communityposts.post_id, communityposts.topic_id, communityposts.post_time,
    communityusers.user_id, , communitytopics.topic_title, communityusers.username,
    communityusers.user_avatar, 
    FROM_UNIXTIME(post_time, '%Y-%c-%d %H:%i') as post time 
FROM
    communityposts,
    communitytopics,
    communityusers
WHERE 
    communityposts.poster_id=communityusers.user_id
    AND communityposts.topic_id=communitytopics.topic_id
ORDER BY post_time DESC LIMIT 5

run myisamcheck on the database, it might be corrupted or you are runnig a rather old version of mysql — Jauzsika
– Jauzsika, Commented Oct 5, 2011 at 21:59
use JOINs instead of cross joining with WHERE statements. — mellamokb
– mellamokb, Commented Oct 5, 2011 at 22:00
Is the key posttime (line 1) supposed to be the same key as post_time (line 5)? — Pete Wilson
– Pete Wilson, Commented Oct 5, 2011 at 22:01
Is that a temporary table that has the corrupt index? It seems likely from the location/filename. Is it possible to get whatever it is that created it to disconnect/reconnect and recreate it for whatever purpose it needs it? — Kenny
– Kenny, Commented Oct 5, 2011 at 22:01
do queries: REPAIR TABLE communityposts; REPAIR TABLE communitytopics; REPAIR TABLE communityusers; — bksi
– bksi, Commented Oct 5, 2011 at 22:07

RolandoMySQLDBA · Accepted Answer · 2011-10-10 20:27:29Z

3

SELECT
    A.*,B.*,C.*,FROM_UNIXTIME(post_time, '%Y-%c-%d %H:%i') as posttime  
FROM
    (
        SELECT id,poster_id,topic_id
        FROM communityposts
        ORDER BY post_time DESC
        LIMIT 5
    ) cpk
    INNER JOIN communityposts A USING (id)
    INNER JOIN communityusers B ON cpk.poster_id=B.user_id
    INNER JOIN communitytopics C USING (topic_id);

If a community post does not have to have a user and a topic, then use LEFT JOINs for the last two joins.

You will need to create a supporting index for the cpk subquqery:

ALTER TABLE communityposts ADD INDEX (posttime,id,poster_id,topic_id);

This query has to be the fastest because the cpk subquery only gets five keys ALL THE TIME.

UPDATE 2011-10-10 16:28 EDT

This query eliminiates the ambiguous topic_id issue:

SELECT
    A.post_id, cpk.topic_id, A.post_time,
    B.user_id, C.topic_title, B.username,
    B.user_avatar, 
    FROM_UNIXTIME(post_time, '%Y-%c-%d %H:%i') as posttime  
FROM
    (
        SELECT id,poster_id,topic_id
        FROM communityposts
        ORDER BY post_time DESC
        LIMIT 5
    ) cpk
    INNER JOIN communityposts A USING (id)
    INNER JOIN communityusers B ON cpk.poster_id=B.user_id
    INNER JOIN communitytopics C ON cpk.topic_id=C.topic_id;

edited Oct 10, 2011 at 20:27

answered Oct 5, 2011 at 22:24

RolandoMySQLDBA

44.4k16 gold badges96 silver badges136 bronze badges

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

2 Comments

Scott Richardson Over a year ago

Thanks Rolando. I note that id needs to be changed to post_id. I tried this query however I got: Column 'topic_id' in from clause is ambiguous

RolandoMySQLDBA Over a year ago

I updated my answer with a query that should eliminate the ambiguous topic_id issue

Galz · Accepted Answer · 2011-10-05 22:10:53Z

0

Then temp table used for sorting the data probably gets too big. I have seen this happen when the /tmp/ runs out of space. The LIMIT clause does not make it any quicker or easier, as the sorting of the full data set has to be done first.

Under some conditions, MySQL does not use a temp table to sort data. You can read about it here: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

If you manage to meet the right conditions (mostly use the correct indexes), it will also peed up your query.

If this doesn't help (in some cases you can't escape the heavy sorting), try to find out how much free space there is on /tmp/, and see if it can be expanded. Also, as sehe mentioned, selecting only the needed columns (instead of *) can make the temp table smaller and is considered best practice anyway (and so is using explicit JOINs instead of implicit ones).

answered Oct 5, 2011 at 22:10

Galz

6,8525 gold badges35 silver badges39 bronze badges

2 Comments

Scott Richardson Over a year ago

Im gonna try the Query below, failing that, I'll try it with just the cols that I need rather than SELECT *.

Scott Richardson Over a year ago

Galz, OK I fixed it. I just selected ONLY the cols that I needed, and now the query is working again.

sehe · Accepted Answer · 2011-10-05 22:30:25Z

0

You could reduce the number of fields selected.

The * operator will select all fields from all (3) tables. This may get big. That said, I think mysql is smart enough to lay this plan out so that it doesn't need to access the data pages except for the 5 rows being selected.
Are you sure that all the involved (foreign) keys are indexed?

Here's my stab:

SELECT posts.*, FROM_UNIXTIME(post_time, '%Y-%c-%d %H:%i') as posttime 
FROM communityposts posts
     INNER JOIN communitytopics topics ON posts.topic_id = topics.topic_id
     INNER JOIN communityusers users ON posts.poster_id = users.user_id
ORDER BY post_time DESC LIMIT 5

edited Oct 5, 2011 at 22:30

answered Oct 5, 2011 at 22:00

sehe

400k49 gold badges475 silver badges673 bronze badges

6 Comments

Scott Richardson Over a year ago

Thanks sehe, going to give this a go now! Will report back.

Scott Richardson Over a year ago

Had to edit line 4.. should be posts.poster_id = users.**user**_id

Scott Richardson Over a year ago

Sadly it's not working - I'm no longer getting the original error, however Im also no longer getting any actual results from my activity feed: trancetribe.com (sidebar)

sehe Over a year ago

@ScottRichardson: I'm sure you know how to revert posts.* to just *? Of course you'll need to include the fields you use. I was just giving an example to show the gist of what I'd explained.

Scott Richardson Over a year ago

Whoops. When changing it back to just *, I get the same original MySQL error again.

|

Collectives™ on Stack Overflow

More Efficient Way To Write MySQL Query?

3 Answers 3

2 Comments

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related