Please optimize the database in the new version after 1.7.2

Question

Q2A can not receive a lot of visitors

the application has loopholes

My suggestion is to fasten the MYSQL queries and index these tables so that the query time on these is lowered.

Comments

Which queries and indexes are you talking about? All the tables have some indexes on them.

---

Please check :

/usr/sbin/mysqld, Version: 5.5.47-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 160118 22:49:36
# User@Host: isalna[isalna] @ localhost []
# Query_time: 14.236989 Lock_time: 0.000025 Rows_sent: 2214 Rows_examined: 2214
SET timestamp=1453153776;
SELECT * FROM `isalna_isalna`.`qa_blobs`;
# Time: 160118 22:49:57
# User@Host: isalna[isalna] @ localhost []
# Query_time: 20.314002 Lock_time: 0.000025 Rows_sent: 257394 Rows_examined: 257394
SET timestamp=1453153797;
SELECT * FROM `isalna_isalna`.`qa_contentwords`;
# Time: 160118 22:51:33
# User@Host: isalna[isalna] @ localhost []
# Query_time: 89.548527 Lock_time: 0.000021 Rows_sent: 697562 Rows_examined: 697562
SET timestamp=1453153893;
SELECT * FROM `isalna_isalna`.`qa_eventlog`;
# Time: 160118 22:52:11
# User@Host: isalna[isalna] @ localhost []
# Query_time: 32.867167 Lock_time: 0.000019 Rows_sent: 150772 Rows_examined: 150772
SET timestamp=1453153931;
SELECT * FROM `isalna_isalna`.`qa_posts`;
# Time: 160118 22:52:43
# User@Host: isalna[isalna] @ localhost []
# Query_time: 31.306731 Lock_time: 0.000022 Rows_sent: 487888 Rows_examined: 487888
SET timestamp=1453153963;
SELECT * FROM `isalna_isalna`.`qa_sharedevents`;
# Time: 160118 22:52:57
# User@Host: isalna[isalna] @ localhost []
# Query_time: 13.837871 Lock_time: 0.000026 Rows_sent: 725838 Rows_examined: 725838
SET timestamp=1453153977;
SELECT * FROM `isalna_isalna`.`qa_titlewords`;

Each of these queries is not indexed and is taking even as high as 89 seconds to execute.

Answer 1

I think those must be from a plugin. I cannot see any queries in Q2A core that do a "SELECT * FROM qa_blobs" for example. I don't think any tables are fetched in their entirely except for the options table. Everything else is filtered in some way (e.g. selecting specific blob rows).