MySQL query or hoster's fault. Who is right?

Question

MySQL

Good evening!

I have a resource on cloud hosting. There is a search script. There is a search in several tables, but only one field has a FULLTEXT index ( articles.article_text_for_search ). The volume of texts on which the search is conducted is large. The number of entries in articles is 3,000.

The fact is that when the search.php script is running (and this is about 8 seconds), then the rest of the pages from the site, if you try to download them, are not immediately given. They are returned only when the search.php script starts to send content to the browser ...

The hoster says that it's my fault. And says nothing more. I'm not a mysql guru, but how can SELECT queries interfere with serving content? (I don’t use LOCK Tables, the type of tables is MyISAM everywhere)

SELECT articles.id<br>
, articles.magazine_id<br>
, articles.issue_id<br>
, articles.article_name<br>
, articles.annotation<br>
, articles.article_text<br>
, SUBSTRING( articles.article_text, 1, 1000) as article_text_1<br>
, articles.section_id<br>
, articles.article_rating<br>
, articles.to_main<br>
, articles.article_unix_add<br>
, magazines.mag_name<br>
, magazines_issues.issue_number<br>
, GROUP_CONCAT( users.user_name ) AS user_names<br>
, GROUP_CONCAT( users.user_surname ) AS user_surnames<br>
, GROUP_CONCAT( tags.tag_name ) AS tag_names<br>
, sections.sec_name<br>
, CONVERT( GROUP_CONCAT( articles_authors.author_id ) USING utf8 ) AS author_ids<br>
, CONVERT( GROUP_CONCAT( articles_tags.tag_id ) USING utf8 ) AS tag_ids<br>
FROM magazines, users, sections, articles_authors<br>
JOIN articles<br>
LEFT JOIN magazines_issues<br>
ON articles.issue_id = magazines_issues.id<br>
LEFT JOIN articles_tags ON articles.id = articles_tags.article_id<br>
AND articles_tags.status = 'active'<br>
LEFT JOIN tags ON tags.id = articles_tags.tag_id<br>
WHERE <br>
(<br>
articles.article_name LIKE '%интересная статья%'<br>
OR MATCH (articles.article_text_for_search) AGAINST ('"интересная статья"' IN BOOLEAN MODE)<br>
OR users.user_name LIKE '%интересная статья%'<br>
OR users.user_surname LIKE '%интересная статья%'<br>
OR magazines.mag_name LIKE '%интересная статья%'<br>
)<br>
AND articles.magazine_id = magazines.id<br>
AND users.id = articles_authors.author_id<br>
AND articles.section_id = sections.id<br>
AND articles.article_status = 'published'<br>
AND articles_authors.article_id = articles.id<br>
AND articles_authors.status = 'active'<br>
GROUP BY articles.id<br>
LIMIT 0, 30<br>

Answer 1

The fact is that when the search.php script is running (which is about 8 seconds), then the rest of the pages from the site, if you try to download them, are not immediately given. They are returned only when the search.php script starts to send content to the browser ...
- +
- Question: if the search.php script is running while trying to open another page in ANOTHER browser - will they be given?

Answer 2

So the database eats up the entire CPU or disk IO, that's the whole trick.

Answer 3

I will not answer your question and will not criticize the solution, but IMHO it is easier and more correct to implement using Sphinx Search

Answer 4

If you do not explicitly specify a lock, the server will set it itself when select - read-lock when update - write-lock. In MyISAM the whole table is locked, in innoDB the row

Answer 5

The problem may be in the php session, if your sessions are stored on disk (/tmp/),
the solution is to convert the sessions to the Database and there will be no such delay.

Answer 6

I remember an anecdote: "Well, you have requests, the database said and hung."
I am more than sure that indexes do not work at all, and given the use of group, we can talk about this without even thinking. Look at the query with explain first and enjoy the horror of what a database has to do.
Here a lot of temporary tables are created, all this is constantly swapped to disk, etc.
Either the query needs to be simplified, or you need to switch to other solutions, sphinx for example.

Answer 7

Or maybe the hoster just has one worker per client? it hung waiting for a response from MySQL and everyone else in the queue