MySQL Query - Help Boards Main Page :)

Cloud

The reason our main page latest posts are missing is because the query is taking 8 seconds...

Can a MySQL head help out?

mysql> explain SELECT thread.lastpost, thread.title, thread.postuserid,thread.postusername,thread.lastposter, thread.replycount, thread.views, user.userid, thread.threadid, thread.forumid, forum.title AS ftitle,thread.iconid FROM thread, user, forum WHERE thread.lastposter = user.username AND thread.open != '10' AND thread.visible = 1 AND thread.forumid = forum.forumid  AND FIND_IN_SET("81", forum.parentlist) ORDER BY thread.lastpost DESC LIMIT 25;
+----+-------------+--------+--------+-------------------------+------------+---------+--------------------------+--------+-----------------------------+
| id | select_type | table  | type   | possible_keys           | key        | key_len | ref                      | rows   | Extra                       |
+----+-------------+--------+--------+-------------------------+------------+---------+--------------------------+--------+-----------------------------+
|  1 | SIMPLE      | thread | range  | open,lastposter,forumid | open       |       1 | NULL                     | 296819 | Using where; Using filesort |
|  1 | SIMPLE      | user   | ref    | username_2,username     | username_2 |     100 | boards.thread.lastposter |      1 | Using where                 |
|  1 | SIMPLE      | forum  | eq_ref | PRIMARY,forumid         | PRIMARY    |       2 | boards.thread.forumid    |      1 | Using where                 |
+----+-------------+--------+--------+-------------------------+------------+---------+--------------------------+--------+-----------------------------+
3 rows in set (0.00 sec)

Civilian_Target

are thread.lastpost and forum.parentlist indexed? Are they indexed individually or in a joint index?

I dunno about mySQL but in DB2 a select_type of SIMPLE means that a tablescan is occuring, select_type of INDEX means you're running an index. 3 tablescans, especially if you use 2^N could easily take 8 seconds on a DB the size of boards.ie (I've seen DB2 take a 0.7 seconds to perform a single tablescan, on a dual Xeon machine!).

I don't know whether you can do this in MySQL, but most DB systems have a "Query Plan" feature which will draw you a diagram of what occurs when the query is run. If you can get a plan for the query you'll make life a lot easier for yourself...

Torak

Cloud wrote:

The reason our main page latest posts are missing is because the query is taking 8 seconds...

Can a MySQL head help out?

mysql> explain SELECT thread.lastpost, thread.title, thread.postuserid,thread.postusername,thread.lastposter, thread.replycount, thread.views, user.userid, thread.threadid, thread.forumid, forum.title AS ftitle,thread.iconid FROM thread, user, forum WHERE thread.lastposter = user.username AND thread.open != '10' AND thread.visible = 1 AND thread.forumid = forum.forumid  AND FIND_IN_SET("81", forum.parentlist) ORDER BY thread.lastpost DESC LIMIT 25;
+----+-------------+--------+--------+-------------------------+------------+---------+--------------------------+--------+-----------------------------+
| id | select_type | table  | type   | possible_keys           | key        | key_len | ref                      | rows   | Extra                       |
+----+-------------+--------+--------+-------------------------+------------+---------+--------------------------+--------+-----------------------------+
|  1 | SIMPLE      | thread | range  | open,lastposter,forumid | open       |       1 | NULL                     | 296819 | Using where; Using filesort |
|  1 | SIMPLE      | user   | ref    | username_2,username     | username_2 |     100 | boards.thread.lastposter |      1 | Using where                 |
|  1 | SIMPLE      | forum  | eq_ref | PRIMARY,forumid         | PRIMARY    |       2 | boards.thread.forumid    |      1 | Using where                 |
+----+-------------+--------+--------+-------------------------+------------+---------+--------------------------+--------+-----------------------------+
3 rows in set (0.00 sec)

The query is using the index on open. I am assuming from the name that it is a single field index.

An index on fields forumid, open and visible on the thread table in that order should speed the query up a lot.

The order of the fields in the index is important.

You might want to include lastpost in the index also (as the last field) which might make a difference. (The optimiser might recognize that it can sort and limit the rows before returning to the table)

CREATE INDEX opvisibforidx ON thread (forumid, open, visible, lastpost)

amen

when I try and resolve this sort of issues
I would always ask
1: when if ever did it last work ?
2: what has changed since then ? new sql, updated os/hw, new app
3: Has the volume of data grown dramatically since it last worked ?
4: do we have a good working backup that we can use to test ?
5: where were the statistics last updated on the table ? i think you need to run ANALYZE TABLE in MySQL on a regular basis (you do in MSSQL as well)
6: Are there any indexes on the tables and are they used in the select statement ?
7: I think its the indexes are incorrect
8: have a look at ANALYZE TABLE this can be run on an entire db if you want

Is the posted query run everytime a user navigates to boards.ie ?
If so I think its a bad idea. You should have a query that runs once very minute and populates a table.
when a user navigates to boards the values from this table should be used to display the last 25 this would be a lot more efficent and reduce your server load (I know they won;t get the most recent but is that that important)
I'm sure there are loads of other things that could also be done
pm if you want
I'd be inteseted to know how you get on

ebooborg

hi there

i had big problems with mysql myself
maybe the solutions to my problems can help you

i have a bulletin board that i wrote myself,
the board got popular and page generation went to unbearable 30seconds! (its a 3ghz server with 1gb ram)
so what was wrong?!
basicaly one of the table got large, very large (2million rows) and it was taking ages to search it
i added an index, upgraded mysql, tweaked few settings, turned on caching and
wham the page generation time went to less than 0.5 seconds

madramor

QUERY:1
// same as your query but using open as the first case in the where section 
// and visible as second
SELECT thread.lastpost,thread.title,thread.postuserid,thread.postusername,thread.lastposter,thread.replycount,thread.views,user.userid,thread.threadid,thread.forumid,forum.title
AS
ftitle,thread.iconid
FROM
thread,user,forum
WHERE
thread.open != '10'
AND
thread.visible = 1
AND
thread.lastposter = user.username 
AND
thread.forumid = forum.forumid
AND
FIND_IN_SET("81", forum.parentlist)
ORDER BY
thread.lastpost DESC LIMIT 25;

if you want help the best way would be to make a DB dump available
for people to play with, also what MySql version

Syth

madramor wrote:

QUERY:1
// same as your query but using open as the first case in the where section 
// and visible as second
SELECT thread.lastpost,thread.title,thread.postuserid,thread.postusername,thread.lastposter,thread.replycount,thread.views,user.userid,thread.threadid,thread.forumid,forum.title
AS
ftitle,thread.iconid
FROM
thread,user,forum
WHERE
thread.open != '10'
AND
thread.visible = 1
AND
thread.lastposter = user.username 
AND
thread.forumid = forum.forumid
AND
FIND_IN_SET("81", forum.parentlist)
ORDER BY
thread.lastpost DESC LIMIT 25;

if you want help the best way would be to make a DB dump available
for people to play with, also what MySql version

A database dump of boards.ie??

Mutant_Fruit

if you want help the best way would be to make a DB dump available
for people to play with, also what MySql version

Or at least some kind of access to an SQL server hosting a backup of the database. I.e. would it be possible to give some of us full access to a backup (i.e. NOT a live copy) of the boards database so we can mess around and try a few different queries to try and sort out the problem.

Unless you've already sorted it yourself...

secret_squirrel

Im not a mysql head (oracle) but did the Mysql version get upgraded at the same time as VB3.5?

If so I reckon you're looking at a change or bug in the optimiser.

Just for pig iron try dropping that != 10 and replacing it with an IN with all the allowed values.

Not something silly like stats not having been run after the upgrade?

Mutant_Fruit

Maybe this is a stupid idea... but i think it makes sense in this situation.

Performance of that query is going to get worse and worse as time goes on, as it has to scan more and more rows in the database. How about changing it slightly to do the following:

1) Create a new table called RecentUpdates.
2) Whenever a thread is updated, the updated thread runs a bit of SQL that will add the relevant info* to the RecentUpdates table, replacing the oldest "thread" in the RecentUpdates table.

That way whenever someone opens the mainpage to see the list of recent threads you just have to return all the rows from the RecentUpdates table, thus stopping the scan of 296819 rows every time.

*relevant info = "SELECT thread.lastpost, thread.title, thread.postuserid,thread.postusername,thread.lastposter, thread.replycount, thread.views, user.userid, thread.threadid, thread.forumid, forum.title AS ftitle,thread.iconid" <-- all that crap.

Zaph0d

To fix this, you need to know whether the WHERE conditions are sargable, whether the indexes on all joining fields are covered, whether any implicit type conversions are taking place etc. This can't be done without knowing the table definitions and indexes. Could you run the commands below and post the output?

DESCRIBE thread;
SHOW INDEX FROM thread;
DESCRIBE user;
SHOW INDEX FROM user;
DESCRIBE forum;
SHOW INDEX FROM forum;

From the EXPLAIN output posted, nearly 300,000 records from the thread table have to be examined each time the query is run. "Using filesort" appears in the extra column of the EXPLAIN output, indicating that an index is unavailable for the ORDER BY thread.lastpost clause.

stevenmu

Not familiar with MySQL, but if thread.lastpost is indexed would adding a filter to only include threads with a lastpost in the last hour help ?

scojones

I notice the latest posts are now on the main page, did you fix it Cloud? If so, how?

BrokenArrows

its not working because its not updating every post