I wanna know if there is a better way to do this query or use indexes or something else. and most of the times its really slow.Īnd the reason I'm doing all this in one query is because I want the result to be in one json response so I can show them the user quickly without doing additional requests. My posts table contains up to 50,000 rows each month, and each row with 3~10kbs of data in avg., so sometimes when I try to run the query (which is frequently run by the admin to make the newsletter, pagination etc) mysql shows this error: too much rows to join, etc. WHERE `feed_id` IN (6, 7) ORDER BY `posts`.`published` DESC LIMIT 40 OFFSET 0īut the problem is this is horrible and not optimized. LEFT JOIN `newsletters` ON `newsletters`.`id` = `newsletter_post`.`newsletter_id` LEFT JOIN `newsletter_post` ON `posts`.`id` = `newsletter_post`.`post_id` LEFT JOIN `categories` ON `categories`.`id` = `category_post`.`category_id` LEFT JOIN `category_post` ON `posts`.`id` = `category_post`.`post_id` So I'm using this query to find posts for the allowed feeds and check the status if a post is in a specific category of this specific newsletter: SELECT DISTINCT `posts`.`id`, `published`, `posts`.`title`, `posts`.`content`, `source_name`, `category_id`, `newsletter_id`, `link_href`, categories.title as category_title Newsletter_post: CREATE TABLE `newsletter_post` (Ĭategory_post: CREATE TABLE `category_post` ( Newsletters: CREATE TABLE `newsletters` ( So I have some other tables too: newsletters, categories, newsletter_post, category_post. So in Newsletter creation page I want to show them a list of posts from the feeds they are allowed to see and also, I show a button to put the posts in specifict categories of that newsletter, if the user previously selected that post, I should show him that and let him remove it from the category. ![]() `updated_at` timestamp NULL DEFAULT NULL,Įvery admin (user) has access to one or more "feeds". ![]() `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `id` int(11) unsigned NOT NULL AUTO_INCREMENT, this is the structure of posts table (truncated): CREATE TABLE `posts` ( rest of query) It tells the optimizer to do it in the order you've specified. I insert the posts in posts table after crawling has completed and assign them a feed_id to identify the source. Since your query looks like all the other tables are more 'lookup' reference only, and your Surveys table is the critical root table, just change. ![]() I have a project where the admin needs to create multiple newsletters with some crawled posts from the web.
0 Comments
Leave a Reply. |