I'm trying to run a query on MySQL database, but the query takes too much time, even though the database is not too large, around 100,000 of rows in each table.I have not defined or created any indexes till now.
These are my tables:
post_feeds:
+-------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | post_id | int(11) | YES | | NULL | | | post_title | varchar(200) | YES | | NULL | | | post_date | int(11) | YES | | NULL | | | post_featured_img | varchar(200) | YES | | NULL | | | post_scope | int(11) | YES | | 0 | | +-------------------+--------------+------+-----+---------+----------------+
post_likes:
+------------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+------------+------+-----+---------+-------+ | post_id | int(11) | NO | PRI | NULL | | | post_likes_count | bigint(20) | NO | | 0 | | +------------------+------------+------+-----+---------+-------+
post_descriptions:
| Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------+-------+ | post_id | int(11) | NO | PRI | NULL | | | post_description | longtext | NO | | NULL | | | post_link | varchar(200) | NO | | NULL | | +------------------+--------------+------+-----+---------+-------+
user_post_bookmarks:
+---------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | int(11) | NO | | NULL | | | post_id | int(11) | NO | | NULL | | +---------+---------+------+-----+---------+----------------+
user_post_likes:
+---------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_id | varchar(45) | NO | | NULL | | | post_id | varchar(45) | NO | | NULL | | +---------+-------------+------+-----+---------+----------------+
This is my query:
SELECT post_feeds.post_id
,post_feeds.post_title
,post_feeds.post_date
,post_feeds.post_featured_img AS post_featured_image
,ifnull(post_likes.post_likes_count ,0) AS post_likes_count
,(user_post_bookmarks.post_id IS NOT NULL) AS post_bookmarked,
,(user_post_likes.post_id IS NOT NULL) AS post_liked
,post_descriptions.post_description
,post_descriptions.post_link
FROM cloneposts_base.post_feeds
LEFT JOIN cloneusers_base.user_post_bookmarks
ON user_post_bookmarks.post_id = post_feeds.post_id
AND user_post_bookmarks.user_id = 1
LEFT JOIN cloneposts_base.post_likes
ON post_likes.post_id = post_feeds.post_id
LEFT JOIN cloneusers_base.user_post_likes
ON user_post_likes.post_id = post_feeds.post_id
AND user_post_likes.user_id = 1
LEFT JOIN cloneposts_base.post_descriptions
ON post_descriptions.post_id = post_feeds.post_id
WHERE post_feeds.post_date<unix_timestamp()
AND (post_scope=0 OR post_scope=1)
ORDER BY
post_feeds.post_date DESC limit 10
The query takes around 4-5 minutes to query all the data. How can I optimize my query or database tables to minimize the query time as low possible, like in milliseconds or seconds?