MySQL query or hosting provider's fault?

Started by Jineshsethia, Mar 27, 2023, 12:03 AM

Previous topic - Next topic

JineshsethiaTopic starter

Good day!
I have a cloud hosting resource that includes a search script. The script performs searches across multiple tables, but the FULLTEXT index is only applied to one specific field (articles.article_text_for_search). The texts being searched are quite large in volume. There are approximately 4,000 entries in the articles table.

Here's the issue: when the search.php script runs (which takes about 8 seconds), other pages on the site become inaccessible if you try to load them. They only become available once the search.php script starts sending content to the browser...

According to the host, it's my fault, but they don't provide any further explanation. I don't consider myself an expert in MySQL, but how can SELECT queries affect the delivery of content? (I'm not using LOCK Tables, and all the tables are of type MyISAM)

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

mxtecsubs

The situation is such that when the search.php script is running (which takes approximately 8 seconds), other pages on the site cannot be immediately accessed or loaded. They become accessible only when the search.php script starts delivering content to the browser...

Now, let's consider a question: if the search.php script is still running, and you try to open another page on the site using a completely different browser, will that page be accessible?
  •  

JanviArora

I won't directly answer your question or provide criticism of the solution, but in my opinion, implementing this using Sphinx Search may be a simpler and more appropriate approach.

When it comes to locks, if you don't explicitly specify one, the server will automatically set a read-lock for select queries and a write-lock for update queries. In the case of MyISAM tables, the entire table is blocked, whereas in InnoDB, it's done at the row level.

Taking a step back, the suggestion of using Sphinx Search brings an interesting alternative to the table. Sphinx Search is known for its efficiency in handling large volumes of text data and providing fast search capabilities. Exploring this option further could potentially lead to a more optimal and scalable solution.

When working with locks in databases, understanding how they are applied and managed can greatly impact performance and concurrency. Depending on the table type, whether it's MyISAM or InnoDB, the locking behavior can vary. Carefully considering the implications of different locking mechanisms is crucial for maintaining a balance between data integrity and responsiveness.

In the end, finding the right approach often involves weighing the pros and cons of various solutions, always keeping scalability and performance in mind.
  •  

AdvanceWebSolutions

The frustration with the ineffective indexes and the complexity of the query is a common challenge in database optimization. Without a proper index strategy, the database may struggle to efficiently process the requested data, resulting in performance issues.

Examining the query plan using the "explain" feature is a great starting point for identifying potential bottlenecks and understanding the underlying problems. This diagnostic tool provides insights into how the database is executing the query, including the creation of temporary tables and disk swapping, which can significantly impact performance.

In situations like this, it's important to explore potential simplifications to the query or even consider alternative solutions like Sphinx Search, which is specifically designed for fast and efficient full-text searching. These alternatives may offer a more streamlined approach to handling the given workload, leading to improved performance and a more stable system overall.
  •  

tevez

There are a few potential reasons why the SELECT queries in your search.php script could be affecting the delivery of content on your site:

1. Resource Usage: Running complex queries, especially when dealing with large volumes of data, can consume a significant amount of server resources, such as CPU and memory. This can result in slower response times for other pages on your site or even cause them to become temporarily inaccessible.

2. Locking: Even though you mentioned that you're not using LOCK Tables explicitly, there could still be some underlying locking happening in the MySQL database. For example, if multiple queries try to write to the same tables simultaneously, they may need to wait for each other, causing delays in serving other requests.

3. Query Optimization: It's possible that the query itself is not optimized for performance. You have several JOIN operations, GROUP_CONCAT, and LIKE statements with wildcard characters (%), which can impact execution speed. Analyzing the query execution plan, adding appropriate indexes, or rewriting the query structure could help improve performance.

4. Database Configuration: The issue may also be related to the configuration settings of your MySQL database. For instance, if the maximum number of connections or concurrent queries allowed is too low, this could contribute to slower response times or unavailability of other pages.

To further investigate the issue and identify the root cause, you could try the following steps:

1. Review the error logs: Check for any error messages or warnings related to the search.php script or the database queries. This might provide additional insights into the problem.

2. Test query performance: Isolate the query and run it directly in an SQL client, such as phpMyAdmin or Sequel Pro. Observe the execution time and resource usage. If it takes a long time or consumes excessive resources, it could indicate a performance issue with the query itself.

3. Consult your host: Reach out to your hosting provider and request more information on why they believe the issue is your fault. Ask if they can provide any server or query logs that might shed light on the problem.

4. Consider database optimizations: If the query is underperforming, you may need to optimize it by adding appropriate indexes, restructuring the query, or splitting it into multiple smaller queries.

5. Evaluate server resources: Assess whether the server resources allocated to your hosting plan are sufficient for running resource-intensive queries. You may need to consider upgrading your plan or exploring alternative hosting options if resource limitations are causing the issue.

By investigating these factors, you should be able to gain a better understanding of why the SELECT queries in your search.php script are impacting the delivery of content on your site.
  •  

astrobestpandit

Determining whether an issue originates from a MySQL query or a hosting provider's fault requires investigation. Check for query optimization, indexing, and efficient database design to ensure the query isn't causing performance problems. If queries are optimized and still slow, it might be related to the hosting environment—insufficient resources, server configuration, or network latency. Collaborate with your hosting provider to identify potential bottlenecks. It's often a combination of both factors; well-optimized queries and a reliable hosting setup are both crucial for optimal database performance.




  •  

ramswamypsychics

Check Query Performance: Start by examining the MySQL query itself. Look for inefficient or long-running queries using tools like MySQL's EXPLAIN statement to analyze query execution plans.

Server Logs: Review server logs for any error messages or performance-related issues. If there are database errors, it might indicate a problem with the query or database configuration.

Server Resources: Monitor server resources such as CPU, memory, and disk usage. If your hosting plan lacks adequate resources, it could lead to slow query performance.

Database Optimization: Optimize the database schema, indexing, and caching to improve query performance. This can often resolve issues that seem to be related to hosting.

Consult Hosting Support: If you've ruled out query and database-related issues, contact your hosting provider's support team. They can assist in identifying server-specific problems or configuration issues.
  •