Home > mysql > Mysql query is very slow

Mysql query is very slow

April 9Hits:1
Advertisement

I am developing a website like any other social networking site using mysql.

I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is working very slow. this process take 2-3 seconds to fetch result from server. It has all the necessary indexes, relations on table. I have used EXPLAIN command to understand it, but I got nothing problematic in it. I can't understand what is the basic problem in it. Please help me.

here is my table structure : Table : UserMaster ~~~~~~~~~~~~~~~~~~

CREATE TABLE `UserMaster` (         `UserID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,     `UserName` varchar(20) DEFAULT NULL,         `EMailID` varchar(50) DEFAULT NULL,       `FirstName` varchar(20) NOT NULL,       `LastName` varchar(20) NOT NULL,       `CityID` mediumint(8) unsigned DEFAULT NULL,       PRIMARY KEY (`UserID`),       UNIQUE KEY `UX_UserMaster_UserName` (`UserName`),       UNIQUE KEY `UX_UserMaster_EMailID` (`EMailID`),         KEY `FK_UserMaster_CityMst_CityID_idx` (`CityID`),           KEY `FK_UserMaster_CountryMst_CountryID_idx` (`CountryID`),       CONSTRAINT `FK_UserMaster_CityMst_CityID`                FOREIGN KEY (`CityID`) REFERENCES `CityMst` (`CityID`)    ON DELETE NO ACTION,       CONSTRAINT `FK_UserMaster_CountryMst_CountryID` FOREIGN KEY CountryID REFERENCES CountryMst (CountryID) ON DELETE NO ACTION ON UPDATE CASCADE  )  ENGINE=InnoDB AUTO_INCREMENT=19722 DEFAULT CHARSET=utf8$$ 

Table : UserFriends ~~~~~~~~~~~~~~~~~~~

CREATE TABLE `UserFriends` (     `FriendID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,       `UserID` mediumint(8) unsigned NOT NULL,       `UserID2` mediumint(8) unsigned NOT NULL,       `RequestDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP,       `Status` tinyint(3) unsigned NOT NULL DEFAULT '2',       `ApprovalDate` timestamp NULL DEFAULT NULL,       PRIMARY KEY (`FriendID`),       UNIQUE KEY `UX_UserFriends_UserID_UserID2` (`UserID`,`UserID2`),       KEY `IX_UserFriens_UserID_ApprovalStatus` (`UserID`,`ApprovalStatus`,`UserID2`,`FriendID`,`RequestDate`,`ApprovalDate`),       KEY `FK_UserFriends_UserMaster_UserID_idx` (`UserID`),       KEY `FK_UserFriends_UserMaster_UserID2_idx` (`UserID2`),       CONSTRAINT `FK_UserFriends_UserMaster_UserID` FOREIGN KEY (`UserID`) REFERENCES `UserMaster` (`UserID`) ON DELETE NO ACTION ON UPDATE CASCADE,      CONSTRAINT `FK_UserFriends_UserMaster_UserID2`  FOREIGN KEY (`UserID2`)  REFERENCES `UserMaster` (`UserID`) ON DELETE NO ACTION ON UPDATE CASCADE  )      ENGINE=InnoDB AUTO_INCREMENT=50825 DEFAULT CHARSET=utf8$$ 

UserID & UserID2 both fields are linked with UserMaster.UserID

Here is my select query :

SELECT upm.UserID, upm.UserName, upm.FirstName,      COALESCE(mf.TotMutualFriends,0) TotMutualFriends FROM UserMaster upm LEFT JOIN CityMst ct      on ct.CityID = upm.CityID LEFT JOIN StateMst st      on st.StateID = ct.StateID LEFT JOIN (      SELECT uf.UserID, COUNT(1) TotMutualFriends              FROM (           SELECT uf.UserID, uf.UserID2, uf.ApprovalStatus         FROM UserFriends uf         UNION ALL         SELECT uf.UserID2 UserID, uf.UserID UserID2, uf.ApprovalStatus         FROM UserFriends uf      ) uf      INNER JOIN (             SELECT IF(uf.UserID = 1, uf.UserID2, uf.UserID) UserID2         FROM UserFriends uf         WHERE ( uf.UserID = 1 OR uf.UserID2 = 1 )           AND uf.ApprovalStatus = 1     ) uf1          on uf1.UserID2 = uf.UserID2     WHERE uf.ApprovalStatus = 1      GROUP BY uf.UserID  ) mf      on mf.UserID = upm.UserID LEFT JOIN (          SELECT DISTINCT usar.UserID2     FROM UserSuggAutoRejct usar     WHERE usar.UserID = 1     UNION      SELECT IF(uf.UserID = 1, uf.UserID2, uf.UserID) UserID2     FROM UserFriends uf     WHERE ( uf.UserID = 1 OR uf.UserID2 = 1)  ) usar      ON usar.UserID2 = upm.UserID WHERE upm.UserStatus IN(10,11) AND upm.UserID <> 1  AND upm.UserID NOT IN (1221,2191)  AND usar.UserID2 IS NULL  ORDER BY ( CASE WHEN COALESCE(mf.TotMutualFriends,0) > 0                  THEN 0 ELSE 1 END ),           ( CASE WHEN COALESCE(mf.TotMutualFriends,0) > 0                  THEN RAND() ELSE NULL END ),          ( CASE upm.CityID   WHEN 1 THEN 0 ELSE 1 END ),           ( CASE upm.CityID   WHEN 1 THEN RAND() ELSE NULL END ),           ( CASE ct.StateID   WHEN 1 THEN 0 ELSE 1 END ),           ( CASE ct.StateID   WHEN 1 THEN RAND() ELSE NULL END ),           ( CASE st.CountryID WHEN 91 THEN 0 ELSE 1 END ),          ( CASE st.CountryID WHEN 91 THEN RAND() ELSE NULL END )  LIMIT 10 

This is performing very slow. It takes 2-3 seconds to evolute.

Answers

SELECT ... FROM ...
JOIN ( SELECT ... ) ON ...
JOIN ( SELECT ... ) ON ...

has an inherent optimization problem. There will be no indexes on those subqueries. If they have, say, 1000 rows each, then it will take a million (1000*1000) operations to do the JOINs.

If these truly nee to be LEFT JOINs (not plain JOINs), then the optimizer cannot even start with one of those subqueries, thereby avoiding one of the 1000x multipliers.

One possible solution is to do CREATE TEMPORARY TABLE mf ( PRIMARY KEY (UserID) ) SELECT ...; -- where the SELECT is the first subquery. Ditto for usar.

Note that I am suggesting creating the necessary index as the temp table is built.

Related Articles

  • Mysql query is very slowApril 9

    I am developing a website like any other social networking site using mysql. I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is working very slow. this process take 2-3 seconds to fetc

  • amazon small instance mysql query too slowJuly 24

    I have mysql database with 1.5 millions records with 2 million categories records. when i do query on categories and city where clause it takes 304989 milliseconds. on localhost it too fast. i tried to modify my.cnf settings. when i try to restart my

  • Slow MySQL query when the IN list is long

    Slow MySQL query when the IN list is longAugust 21

    I have 3 tables, namely: product, product_has_category, category: CREATE TABLE IF NOT EXISTS `product` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `des` text COMMENT 'description', `price` decimal(10,2) DEFAULT NULL COMMENT 'Price of p

  • does SQL server have the equivalent of MySQL query logsSeptember 19

    I have used MySQL for a long time and have found the general query log and slow query log to be very useful in finding out what is happening on the server, and in identifying bottlenecks. Now I need to use SQL Server. Does SQL server have similar log

  • Why is the MySQL query cache disabled by default?March 3

    I've a pretty simple question... Why is the MySQL query cache disabled by default? In most packaged versions of MySQL I've come across the defaults values are: query_cache_type = 1 query_cache_size = 0 This essentially disables the query cache by def

  • How can I cap the MySQL query log size?August 10

    I have Investigated enabling MySQL query logging. At some point, soon, it would be nice to enable this on my companies production server so that if there is ever a question about queries run against the database, we can see them. I have found: Add th

  • format of mysql query logMay 19

    What is the format of the mysql query log? In particular, for lines like: 133 Query commit 133 Query rollback What does '133' stand for? And is it true that each line represents a round trip communication to the database (i.e., they are not batched)?

  • Make this MySQL query more elegant &/or efficientDecember 17

    I have created a MySQL query that works, but I feel there must be a better way. The query will be used by a PHP script whose purpose is to assign conditions & subconditions to new participants in an online experiment based on how many times each comb

  • How to determine why a MySQL server has become slow and requests sometimes time out?March 4

    I'm working on my first project that makes heavy use of a MySQL database and am struggling to understand performance issues I'm having. Most of the time, responses are returned quickly from the server, but when I have certain scripts that make a lot

  • MySQL query optimization for legacy application

    MySQL query optimization for legacy applicationMarch 28

    I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone. The fact that they have actual data now years later

  • How to decide whether to enable MySQL query cache using InnoDBApril 17

    How should the decision to enable / disable the MySQL query cache be made, on a server that uses just InnoDB tables. Say for example, if the cache is enabled, then how should the output of: SHOW STATUS LIKE 'Qcache%'; be interpreted to make the decis

  • How to optimize this MySQL query?

    How to optimize this MySQL query?June 9

    Hi can someone help me to optimize this MySQL query, it's taking very long to complete, I have set indexes correctly, but it seems that string operations are slowing this query SELECT vendor_products.*, (SELECT Count(*) FROM products WHERE gtin = ven

  • MySQL performance issue - intermittently slow queriesOctober 16

    I manage an intranet application used by around 50 staff in a few locations. The application is hosted on a Windows Server 2008 VM with 8GB RAM and MySQL (5.5) is running on the same server as IIS. I have slow query logging enabled and noticed that c

  • mysql query taking differently longJanuary 2

    I need some enlightenment on a MySQL query issue: I recently found out that a select query is taking differently long depending on how large ibdata1 is (at least that's how it seems to me). To be specific: When I empty the whole table that is being f

  • MySQL query refactorJanuary 19

    I have the following table schema: t_1 t_2 t_3 t_4 t_1 has one to many relationship with t_2. t_2 has one to one relationship with t_3 (but potentially t_2 does not have anything in t_3 or t_4, however I do want to know about these records as a user

  • SQL Query is really slow, how to improve?January 26

    EDIT: It seems the problem lies in the help_file table, which contains the actual binary data of the .pdf files which is being loaded everytime the table is selected, is there any way to circumvent this? I don't want to load all the data everytime. I

  • MYSQL query hangs in sending state

    MYSQL query hangs in sending stateFebruary 3

    I have mysql query which hangs in sending data phase. When analyzing slow query logs Rows_sent: 176975081 Rows_examined: 408406766 These numbers of rows are greater than the number of rows present in table. Here is the query select md.Event_id,db.Lis

  • i have comm in mysql query September 12

    If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$

  • MySQL Query Browser to run 400+ Insert Statements?July 21

    I'm manually duplicating a few records and would like to paste the INSERT statements directly into Query Browser, but I have only ever performed one INSERT at a time with QB. Is it possible to insert, and run, 400+ inserts one after another without h

  • MySQL Query Browser mysteriously can't connectAugust 28

    I have a problem with an end-user who can't connect to their MySQL Server. The facts: Client is Windows XP on a corporate Domain. Group policy has disabled Windows Firewall and there is no other firewalling software installed. The software MySQL's ow

Copyright (C) 2017 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.638 s.