Profile My Little Forum (General)

by danielb987, Monday, March 20, 2017, 12:29 (3 days ago)

Another thread diskusses how to improve the speed of the forum. The first question to ask in order to solve that is how long time each sql query takes. This process is called profiling.

The way this works is to replace each call to mysqli_query with a call to profile_mysqli_query. You then use the parts of the forum you want to test and after that use the script profile_result.php to see the result.

To use the script, create the profile tables using the sql queries in profile_mlf.sql.

Then add the statement "include('profile_mysqli.php');" to the top of the index.php file.

And then replace all occurencies of mysqli_query with profile_mysqli_query.

I have a complete installation of My Little Forum version 2.4.2 with profiling. Se below.

The function profile_mysqli_query is in the file profile_mysqli.php.txt. Please note that you have to change the variable $forum_folder in the top of the file to the location on the server of the forum.

To show the result, run the script profile_result.php.

"Show profile data per file and line" shows a summary of all sql queries of each row in a file. A particular row may have several different queries, for example different id, but all queries on the same row are added up.

"Show profile data per query" shows every single query. So even if a row has several identical sql queries, each of them are shown individual.

"Clear profile data" clears the profile tables. No question is asked!

The script is provided as freeware. Feel free to change it as you like. This is a quick hack and may have bugs. Use at own risk.

Important note!!!
This script is slow and must not be run on a public forum! The profiler adds extra sql queries to each sql query, with the result of a lot of overhead. But it is nessesary to find which sql queries that are causing the problem.

Best regards,
Daniel

Profile My Little Forum

by danielb987, Monday, March 20, 2017, 12:30 (3 days ago) @ danielb987

Complete installation of My Little Forum version 2.4.2 with profiling:
mjtrafik.se/profile_my_little_forum/profile_mlf.zip

Profile My Little Forum (reformatted)

by danielb987, Monday, March 20, 2017, 12:55 (3 days ago) @ danielb987
edited by Auge, Tuesday, March 21, 2017, 13:42

[edit]Proloque: I reformatted the SQL-queries for improvoed readability.

Auge
[/edit]

My forum has 192 000 entries. If I simply reloads the main page of the forum, one single query takes 33 ms, while the rest takes less than 1 ms. However, if I go to an entry, then clear the profile data, and then go to the main page of the forum by clicking on the head line, I get a number of VERY slow SQL queries.

includes/index.inc.php, line 187. Time 1 062 milliseconds!

SELECT
  id,
  pid,
  tid,
  name,
  user_name,
  ft.user_id,
  UNIX_TIMESTAMP(ft.time) AS time,
  UNIX_TIMESTAMP(ft.time + INTERVAL 0 MINUTE) AS timestamp,
  UNIX_TIMESTAMP(last_reply) AS last_reply,
  subject,
  category,
  rst.user_id AS req_user 
FROM mlf2_entries AS ft
  LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id=ft.user_id
  LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id
    AND rst.user_id = 0
  WHERE spam=0
    AND category IN (0, 4, 5, 6, 7, 8, 9, 25, 14, 23, 19, 22, 24, 10, 21, 11, 12, 13, 15, 16, 26, 18, 20, 27)
  ORDER BY ft.time DESC
  LIMIT 40

includes/main.inc.php, line 336. Time 581 milliseconds!

SELECT
  COUNT(*)
FROM mlf2_entries
  WHERE pid = 0
    AND spam = 0
    AND category IN (0, 4, 5, 6, 7, 8, 9, 25, 14, 23, 19, 22, 24, 10, 21, 11, 12, 13, 15, 16, 26, 18, 20, 27)

includes/index.inc.php, line 69. Time 578 milliseconds!

SELECT
  id,
  tid
FROM mlf2_entries
  WHERE pid = 0
    AND spam = 0
    AND category IN (0, 4, 5, 6, 7, 8, 9, 25, 14, 23, 19, 22, 24, 10, 21, 11, 12, 13, 15, 16, 26, 18, 20, 27)
  ORDER BY sticky DESC,
    time DESC
  LIMIT 0, 30

includes/main.inc.php, line 339. 528 milliseconds!

SELECT
  COUNT(*)
FROM mlf2_entries
  WHERE category IN (0, 4, 5, 6, 7, 8, 9, 25, 14, 23, 19, 22, 24, 10, 21, 11, 12, 13, 15, 16, 26, 18, 20, 27)
    AND spam = 0

includes/main.inc.php, line 344. 517 milliseconds!

SELECT
  COUNT(*)
FROM mlf2_entries
  WHERE spam = 1

Best regards,
Daniel

Profile My Little Forum - some suggestions

by danielb987, Monday, March 20, 2017, 14:03 (3 days ago) @ danielb987

Some suggestions to improve response time

includes/index.inc.php, line 187. Time 1 062 milliseconds!

This query has several left joins. Since we are only interested in the latest 40 entries in the table mlf2_entries, I suggest that you first do a sql query from mlf2_entries. Then you use a for loop in php to do the rest of the queries. In my database, mlf2_entries has 193 000 rows, mlf2_read_entries has 5 000 rows and mlf2_userdata has 2 000 rows.

Doing left join can be very expensive.

----------------------------

includes/main.inc.php, line 344. Time 517 milliseconds!

SELECT COUNT(*) FROM mlf2_entries WHERE spam = 1

The query only seems useful for admins and moderators. The query is always run, even if I'm not logged in. I suggest that it is only run for admins and moderators.

----------------------------

includes/main.inc.php, line 336. Time 581 milliseconds!
includes/main.inc.php, line 339. Time 528 milliseconds!

These are two sql queries that counts the rows in mlf2_entries. Each has it's own where-clause, but they only returns a single number, the count of rows.

From my point of view, I don't see why these must be exactly correct. The number of posts and the number of threads on the bottom of the page is fun statistics, but it is in no way important. Who cares if the number of entries are 320 000 or 350 000? From my point of view, the statistics like the number of entries and number of topics is something that can be updated once per day and doesn't need to be more accurate than that, at least not for a big forum

For a small forum, there may be a different thing. A web master of a forum with 1000 entries and 100 topics may want to have the number more accurate, but for big forums, I don't see the point of having to update it more than once a day.

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Monday, March 20, 2017, 17:36 (3 days ago) @ danielb987

Hi,

thank you for your detailed analysis.

SELECT COUNT(*) FROM mlf2_entries WHERE spam = 1
includes/main.inc.php, line 336. Time 581 milliseconds!
includes/main.inc.php, line 339. Time 528 milliseconds!

I think, the spam-column (and the user_id) should be an indexed column. Can you add this index to your database and run the analysis again?

ALTER TABLE `mlf2_entries` ADD INDEX(`spam`);
ALTER TABLE `mlf2_entries` ADD INDEX(`user_id`);

The number of posts and the number of threads

... can be static (i.e. updated by a SQL trigger)


/Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Profile My Little Forum - some suggestions

by danielb987, Monday, March 20, 2017, 18:27 (3 days ago) @ Milo

I think, the spam-column (and the user_id) should be an indexed column. Can you add this index to your database and run the analysis again?

ALTER TABLE `mlf2_entries` ADD INDEX(`spam`);
ALTER TABLE `mlf2_entries` ADD INDEX(`user_id`);

-

That was a huge improvement of two of the queries!

-

includes/main.inc.php, line 344:

SELECT COUNT(*) FROM mlf2_entries WHERE spam = 1

This query went from 500 millisecond to 0,5 milliseconds!

----

includes/main.inc.php, line 336:

SELECT COUNT(*) FROM mlf2_entries WHERE pid = 0 AND spam = 0

This query went from 600 milliseconds to 150 milliseconds!

----

Before the improvement, the top five queries took about 3,2 seconds in total. After the improvement, the top five queries took about 2,2 seconds in total.

Best regards,
Daniel

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Monday, March 20, 2017, 22:12 (3 days ago) @ danielb987

Hi,

That was a huge improvement of two of the queries!

Okay. Thank you! I believe, we should check all columns in WHERE-conditions and/or the column-conditions in JOIN statements (e.g. <foo> ON <bar>). These columns must have an INDEX.

/Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Avatar

Profile My Little Forum - some suggestions

by Auge ⌂ @, Tuesday, March 21, 2017, 08:03 (3 days ago) @ Milo

Hello

Okay. Thank you! I believe, we should check all columns in WHERE-conditions and/or the column-conditions in JOIN statements (e.g. <foo> ON <bar>). These columns must have an INDEX.

I don't know, if it is useful, to set every column, that is/could be membered in a JOIN, as an index. To manage the indexes takes also executing time. But the often in JOINs used
columns should be indexed. The first column, that came in my mind while reading this thread is the column category of the entries table.

Tschö, Auge

--
further development of mlf1

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Tuesday, March 21, 2017, 08:30 (3 days ago) @ Auge

Hi,

To manage the indexes takes also executing time.

Of course, but it seems to be small. The column user_id was not indexed but, I believe, it is an important and often used column for joining - why this column is not set as FOREIGN KEY?

The first column, that came in my mind while reading this thread is the column category of the entries table.

This column is set as key e.g.

KEY category (category)

. Isn't it similar to an INDEX?

/Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Avatar

Profile My Little Forum - some suggestions

by Auge ⌂ @, Tuesday, March 21, 2017, 13:14 (2 days ago) @ Milo

Hello

To manage the indexes takes also executing time.

Of course, but it seems to be small.

I didn't want to argue against the use of INDEX. I only wants to advice against the creation of indexes for every single column, that is in use for JOINs.

The column user_id was not indexed but, I believe, it is an important and often used column for joining

Yes, you are right without a doubt.

- why this column is not set as FOREIGN KEY?

At the time, when Alex defined the table structure of mlf (mlf1, the structure for mlf2 based on that), he and also no one else of us was aware of FOREIGN KEYS to tie the tables to each other and other the abilities of a MySQL-server (that evolved a lot since then). So this feature was never implemented in mlf.

The first column, that came in my mind while reading this thread is the column category of the entries table.


This column is set as key e.g.

KEY category (category)

. Isn't it similar to an INDEX?

Yes, it is and yes, it is. :-)

Tschö, Auge

--
further development of mlf1

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Tuesday, March 21, 2017, 17:41 (2 days ago) @ Auge

Hi,

I only wants to advice against the creation of indexes for every single column, that is in use for JOINs.

I think, we should do it. Indexing all columns that are used for constrains or that are used to combine two (or more) tables because we join the tables (or restrict the result) by these columns, so, they are more important than other ones. Lets take a look to the slow SQL statement:

SELECT
  id,
  pid,
  tid,
  name,
  user_name,
  ft.user_id,
  UNIX_TIMESTAMP(ft.time) AS time,
  UNIX_TIMESTAMP(ft.time + INTERVAL 0 MINUTE) AS timestamp,
  UNIX_TIMESTAMP(last_reply) AS last_reply,
  subject,
  category,
  rst.user_id AS req_user 
FROM mlf2_entries AS ft
  LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id=ft.user_id
  LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id
    AND rst.user_id = 0
  WHERE spam=0
    AND category IN (0, 4, 5, 6, 7, 8, 9, 25, 14, 23, 19, 22, 24, 10, 21, 11, 12, 13, 15, 16, 26, 18, 20, 27)
  ORDER BY ft.time DESC
  LIMIT 40

The following columns are important to join or restrict the output:

  • mlf2_userdata.user_id
  • mlf2_entries.id
  • mlf2_entries.category
  • mlf2_entries.user_id
  • mlf2_entries.spam
  • mlf2_entries.time
  • mlf2_read_entries.posting_id
  • mlf2_read_entries.user_id


Following this line of reasoning, we can optimise this query by:

mlf2_userdata.user_id is primary key, thus, nothing to do
mlf2_entries.id is primary key, thus, nothing to do
mlf2_entries.category is indexed, thus, nothing to do

mlf2_entries.user_id is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`user_id`);

(as already suggested)

mlf2_entries.spam is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`spam`);

(as already suggested)

mlf2_entries.time is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`time`);

mlf2_read_entries.posting_id and mlf2_read_entries.user_id are joined primary keys. They have to indexed separately, see links above.

ALTER TABLE `mlf2_read_entries` ADD INDEX(`user_id`);
ALTER TABLE `mlf2_read_entries` ADD INDEX(`posting_id`);

That's my suggestion. I cannot check the benefit, because I don't have a forum that has so many threads/postings.

/Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Profile My Little Forum - some suggestions

by danielb987, Tuesday, March 21, 2017, 20:30 (2 days ago) @ Milo

The test I currently do is that I go to an entry, then clear the profile data, then go to the main page and then show the result here. The reason for me to do this test is that there are many people reading the forum but only a few posting to the forum. At 20:30 local time in Sweden, my forum has 60 visitors but there have only been 36 posts in the last 24 hours. So it is more important that the forum is fast to read than it is fast to post.

mlf2_entries.user_id is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`user_id`);

(as already suggested)

This I have already done in my database.

mlf2_entries.spam is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`spam`);

(as already suggested)

This I have already done in my database.

------

mlf2_entries.time is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`time`);

By adding this index alone, together with the two indexes above which I have added previously, the query in includes/index.inc.php at line 187 went from 1 000 milliseconds to 22 milliseconds! A very huge improvement!

----

mlf2_read_entries.posting_id and mlf2_read_entries.user_id are joined primary keys. They have to indexed separately, see links above.

ALTER TABLE `mlf2_read_entries` ADD INDEX(`user_id`);
ALTER TABLE `mlf2_read_entries` ADD INDEX(`posting_id`);

I cannot see any benefit from these two indexes at this point, but my table mlf2_read_entries is small. I installed a new MLF forum and imported the data from an old MLF forum, so this table was clean after the update.

When the table starts to grow much bigger, the indexes will probably be much more important than now.

----------

After these new indexes, the status for me is as follows:

includes/index.inc.php, line 69: about 600 milliseconds

includes/main.inc.php, line 339: about 600 milliseconds

includes/main.inc.php, line 336: about 120 milliseconds

includes/index.inc.php, line 187: about 22 milliseconds
(this was the query that before took over 1 000 milliseconds)

Best regards,
Daniel

Avatar

Profile My Little Forum - some suggestions

by Auge ⌂ @, Tuesday, March 21, 2017, 21:22 (2 days ago) @ danielb987

Hello

mlf2_entries.time is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`time`);


By adding this index alone, together with the two indexes above which I have added previously, the query in includes/index.inc.php at line 187 went from 1 000 milliseconds to 22 milliseconds! A very huge improvement!

Wow

mlf2_read_entries.posting_id and mlf2_read_entries.user_id are joined primary keys. They have to indexed separately, see links above.

ALTER TABLE `mlf2_read_entries` ADD INDEX(`user_id`);
ALTER TABLE `mlf2_read_entries` ADD INDEX(`posting_id`);


I cannot see any benefit from these two indexes at this point, but my table mlf2_read_entries is small. I installed a new MLF forum and imported the data from an old MLF forum, so this table was clean after the update.

When the table starts to grow much bigger, the indexes will probably be much more important than now.

That's what I and apparently Milo expect. In a 2.4.x forum with many active registered users, the table will grow and it will grow fast. Because of the SELECTs for the forum entries, which JOINs this table in most cases and on both columns, it is good to have an INDEX on both fields separately.

SELECTFROM mlf2_entries AS ft
  LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id=ft.user_id
  LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id
    AND rst.user_id = 4

After these new indexes, the status for me is as follows:

includes/index.inc.php, line 69: about 600 milliseconds

includes/main.inc.php, line 339: about 600 milliseconds

includes/main.inc.php, line 336: about 120 milliseconds

includes/index.inc.php, line 187: about 22 milliseconds
(this was the query that before took over 1 000 milliseconds)

That's much better, even the two queries with more than 600ms execution time are still slow.

Thank you for your collaboration.

Tschö, Auge

--
further development of mlf1

Profile My Little Forum - some suggestions

by danielb987, Wednesday, March 22, 2017, 04:58 (1 day, 19 hours, 3 min. ago) @ Auge

mlf2_read_entries.posting_id and mlf2_read_entries.user_id are joined primary keys. They have to indexed separately, see links above.

ALTER TABLE `mlf2_read_entries` ADD INDEX(`user_id`);
ALTER TABLE `mlf2_read_entries` ADD INDEX(`posting_id`);


I cannot see any benefit from these two indexes at this point, but my table mlf2_read_entries is small. I installed a new MLF forum and imported the data from an old MLF forum, so this table was clean after the update.

When the table starts to grow much bigger, the indexes will probably be much more important than now.


That's what I and apparently Milo expect. In a 2.4.x forum with many active registered users, the table will grow and it will grow fast. Because of the SELECTs for the forum entries, which JOINs this table in most cases and on both columns, it is good to have an INDEX on both fields separately.

I wanted to see what would happen with more data in the table mlf2_read_entries so I wrote a simple php script that reads mlf2_userdata and mlf2_entries and then add valid but random data to the mlf2_read_entries table. Both user_id, entry_id and time is random. Before, the mlf2_read_entries table had 4 800 rows, now it has 190 000 rows.

I have also removed the two suggested indexes so I now only have (user_id, posting_id) as primary key in the table and no other indexes in this table.

The strange thing is that the sql query in includes/index.inc.php at line 187 still only takes about 22 milliseconds. I thought the reason is that the query checks for user_id = 0, and there is no user with user_id = 0 in the table.

----

So in order to check my idea, I logged in to the forum using my ordinary user with user_id 7843 and then tried again. But no difference. I still only have about 22 milliseconds on this query. There are 200 rows in the table with user_id = 7843.

----

The query I am talking about here is the following query:

SELECT
  id,
  pid,
  tid,
  name,
  user_name,
  ft.user_id,
  UNIX_TIMESTAMP(ft.time) AS time,
  UNIX_TIMESTAMP(ft.time + INTERVAL 0 MINUTE) AS timestamp,
  UNIX_TIMESTAMP(last_reply) AS last_reply,
  subject,
  category,
  rst.user_id AS req_user
FROM mlf2_entries AS ft
  LEFT JOIN mlf2_userdata ON mlf2_userdata.user_id=ft.user_id
  LEFT JOIN mlf2_read_entries AS rst ON rst.posting_id = ft.id
    AND rst.user_id = 7843
  WHERE spam=0
    AND category IN (0, 4, 5, 6, 7, 8, 9, 25, 14, 23, 19, 22, 24, 10, 21, 11, 12, 13, 15, 16, 26, 18, 20, 27)
  ORDER BY ft.time
  DESC LIMIT 40

Best regards,
Daniel

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Wednesday, March 22, 2017, 20:58 (1 day, 3 hours, 3 min. ago) @ danielb987

Hi,

I thought the reason is that the query checks for user_id = 0, and there is no user with user_id = 0 in the table.

This user_id is reserved for unregistered users. Depending on your forum settings, this kind of user-type does/doesn't exist.

/Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Wednesday, March 22, 2017, 08:49 (1 day, 15 hours, 12 min. ago) @ danielb987

Hi,

So it is more important that the forum is fast to read than it is fast to post.

Yes, your are right. The indexing-process will decelerate the INSERT/UPDATE/DELETE statements but, I believe, it is a quite good compromise.

By adding this index alone, together with the two indexes above which I have added previously, the query in includes/index.inc.php at line 187 went from 1 000 milliseconds to 22 milliseconds! A very huge improvement!

Wow that's more than I expected. Fine.

----------

After these new indexes, the status for me is as follows:

includes/index.inc.php, line 69: about 600 milliseconds

includes/main.inc.php, line 339: about 600 milliseconds

includes/main.inc.php, line 336: about 120 milliseconds

includes/index.inc.php, line 187: about 22 milliseconds
(this was the query that before took over 1 000 milliseconds)

We will take a closer look to this queries and give some suggestions for indexing.

regards
Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Wednesday, March 22, 2017, 20:50 (1 day, 3 hours, 10 min. ago) @ danielb987

Hi Daniel,

thank you for supporting us! I received your mail. I will answer you as soon as I have a reply from Auge.

After these new indexes, the status for me is as follows:

includes/index.inc.php, line 69: about 600 milliseconds

Okay, this query is related to the mlf2_entries


mlf2_entries.pid is already indexed --> nothing to optimize.
mlf2_entries.spam is already indexed --> nothing to optimize.
mlf2_entries.sticky is already indexed --> nothing to optimize.

mlf2_entries.time is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`time`);


mlf2_entries.last_reply is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`last_reply`);

includes/main.inc.php, line 339: about 600 milliseconds
includes/main.inc.php, line 336: about 120 milliseconds

Are you sure? mlf2_entries.pid, mlf2_entries.category as well as mlf2_entries.spam are indexed.

includes/index.inc.php, line 187: about 22 milliseconds
(this was the query that before took over 1 000 milliseconds)

Okay, I think, this one is optimized. ;-)

Kind regards,
Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Profile My Little Forum - some suggestions

by danielb987, Wednesday, March 22, 2017, 21:51 (1 day, 2 hours, 9 min. ago) @ Milo

Hi Micha,

mlf2_entries.time is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`time`);


mlf2_entries.last_reply is not indexed:

ALTER TABLE `mlf2_entries` ADD INDEX(`last_reply`);

The computer that I use for these tests is busy with another task at the moment but I will test it tomorrow.

includes/main.inc.php, line 339: about 600 milliseconds
includes/main.inc.php, line 336: about 120 milliseconds


Are you sure? mlf2_entries.pid, mlf2_entries.category as well as mlf2_entries.spam are indexed.

Yes. Both the queries at line 336 and 339 do a COUNT(*).

This page says that COUNT(*) is slow on InnoDB tables:
http://stackoverflow.com/questions/1332624/speeding-up-row-counting-in-mysql

Two quotes from that page:
InnoDB can be very slow to perform count(*) type queries, cause it is designed to allow for multiple concurrent views of the same data. So at any point in time, its not enough to go to the index to get the count.

InnoDB does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a SELECT COUNT(*) FROM t statement, InnoDB must scan an index of the table, which takes some time if the index is not entirely in the buffer pool.

----

The difference between the query at row 336 and the query at row 339 is that the query at row 336 returns COUNT = 28 000 and the query at line 339 returns COUNT = 192 000. This explains why the query at line 336 is five times faster.

Best regards,
Daniel

Profile My Little Forum - some suggestions

by danielb987, Tuesday, March 21, 2017, 20:36 (2 days ago) @ Milo
edited by Milo, Wednesday, March 22, 2017, 08:17

That's my suggestion. I cannot check the benefit, because I don't have a forum that has so many threads/postings.

Micha: Could you please send me an email? I have address <REMOVED BY MILO TO AVOID SPAM>

Best regards,
Daniel

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Wednesday, March 22, 2017, 08:20 (1 day, 15 hours, 41 min. ago) @ danielb987

Hi,

it is good to hear that the indexing of the columns improve the performance.

I removed your mail-address in your posting to avoid spam. ;-)

/Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Profile My Little Forum - some suggestions

by danielb987, Wednesday, March 22, 2017, 08:32 (1 day, 15 hours, 28 min. ago) @ Milo

I removed your mail-address in your posting to avoid spam. ;-)

Thanks!

/ Daniel

Avatar

Profile My Little Forum - some suggestions

by Auge ⌂ @, Tuesday, March 21, 2017, 21:08 (2 days ago) @ Milo

Hello

That's my suggestion.

After reading I think, your suggestion is reasonable. I altered the table structure of my testing installation.

I cannot check the benefit, because I don't have a forum that has so many threads/postings.

"Not so many posts." pfft.

Your forums with a few hundred threads and a few thousand entries is so much more, than I can offer (13 entries in 7 threads). ;-)

A further field, which is often used in WHERE-clauses is the field user_type from the table mlf_userdata. Maybe the column user_name could be indexed too (user lists, search in user area …).

ALTER TABLE `mlf2_userdata` ADD INDEX(`user_type`);
ALTER TABLE `mlf2_userdata` ADD INDEX(`user_name`);

Any comments?

Tschö, Auge

--
further development of mlf1

Avatar

Profile My Little Forum - some suggestions

by Milo ⌂, Wednesday, March 22, 2017, 08:22 (1 day, 15 hours, 38 min. ago) @ Auge

Hi,

Any comments?

sounds logical. +1

/Micha

--
Surveyor-Software: Geodetic Network Adjustment & Deformation-Analysis and Transformation

Profile My Little Forum - some suggestions

by danielb987, Wednesday, March 22, 2017, 08:35 (1 day, 15 hours, 26 min. ago) @ Auge

ALTER TABLE `mlf2_userdata` ADD INDEX(`user_type`);
ALTER TABLE `mlf2_userdata` ADD INDEX(`user_name`);

Should I test this? If so, how? Which operations on the forum, like viewing a thread or going to the main page, would use these indexes?

/ Daniel

Avatar

Profile My Little Forum - some suggestions

by Auge ⌂ @, Wednesday, March 22, 2017, 09:17 (1 day, 14 hours, 44 min. ago) @ danielb987

Hello

ALTER TABLE `mlf2_userdata` ADD INDEX(`user_type`);
ALTER TABLE `mlf2_userdata` ADD INDEX(`user_name`);


Should I test this?

It would be nice, to test this.

Which operations on the forum, like viewing a thread or going to the main page, would use these indexes?

As I wrote:

A further field, which is often used in WHERE-clauses is the field user_type from the table mlf_userdata. Maybe the column user_name could be indexed too (user lists, search in user area …).

- The check for access to restricted categories depends on the user type.
- The user list can be ordered by the user type and by the user name.
- The search for a user name depends (how surprisingly ;-)) on the user name.

With exception of the first example these are more or less rare cases. But these colums are unique (user_name) respectively a quasi index (user_type) that is used here and there in WHERE-clauses or JOINs.

Tschö, Auge

--
further development of mlf1

Avatar

Profile My Little Forum - some suggestions

by Auge ⌂ @, Tuesday, March 21, 2017, 22:21 (2 days ago) @ Milo

Hello

I begun the work on the queries for the installation and update scripts. Please do not merge this until the last commits are added (not yet done).

Tschö, Auge

--
further development of mlf1

RSS Feed of thread
powered by my little forum