CODEWITHSUNDEEP
mysql
Suic
Suic

Reputation: 2501

Terrible and slow query

I have some speed problems with query, that shows list of users in my DB. I want to show list of users with traffic info and the last employee who works with user.

DB looks like this:

users table (contains users info):

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ip` tinytext NOT NULL,
 `name` varchar(64) NOT NULL,

... some other fields

 PRIMARY KEY (`id`),
 UNIQUE KEY `name` (`name`),
 KEY `ip` (`ip`(15)) USING BTREE,
)

users_trf table (contains information about users traffic; uid - id of users from users table):

CREATE TABLE `users_trf` (
 `uid` int(11) unsigned NOT NULL,
 `uip` varchar(15) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
 `in` bigint(20) NOT NULL DEFAULT '0',
 `out` bigint(20) NOT NULL DEFAULT '0',
 `test` tinyint(4) NOT NULL,
 UNIQUE KEY `uid` (`uid`),
 KEY `test` (`test`)
)

employees with list of all employees:

CREATE TABLE `employees` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `full_name` varchar(16) NOT NULL,
 PRIMARY KEY (`id`)
)

and log table where I store data about jobs which employee did with client (uid - id of the client from users table, mid - id of employees from employees table):

CREATE TABLE `employees_log` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `uid` int(10) unsigned NOT NULL,
 `mid` int(10) unsigned NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `note` text NOT NULL,
 PRIMARY KEY (`id`)
)

My query: 

SELECT SQL_CALC_FOUND_ROWS *
FROM users u
LEFT JOIN users_trf t ON u.id = t.uid
LEFT JOIN (

SELECT e2.full_name, e1.uid, e1.mid AS moid
FROM employees_log e1
LEFT JOIN employees e2 ON e1.mid = e2.id
WHERE NOT
EXISTS (

SELECT *
FROM employees_log e3
WHERE e1.uid = e3.uid
AND e1.id < e3.id
)
) e ON e.uid = u.id
LIMIT 0 , 50

it works very slow, I think the reason of this is this subquery (I'm trying to select the last employee who works with client):

SELECT e2.full_name, e1.uid, e1.mid AS moid
FROM employees_log e1
LEFT JOIN employees e2 ON e1.mid = e2.id
WHERE NOT
EXISTS (

SELECT *
FROM employees_log e3
WHERE e1.uid = e3.uid
AND e1.id < e3.id
)

Is it possible to speed up my query?

UPD: I added index ALTER TABLE employees_log ADD INDEX ( uid, id ); and query become 2 times faster, but can I make it more faster? 

+----+--------------------+------------+--------+---------------+---------+---------+-------------+-------+--------------------------+
| id | select_type        | table      | type   | possible_keys | key     | key_len | ref         | rows  | Extra                    |
+----+--------------------+------------+--------+---------------+---------+---------+-------------+-------+--------------------------+
|  1 | PRIMARY            | u          | ALL    | NULL          | NULL    | NULL    | NULL        | 12029 |                          |
|  1 | PRIMARY            | t          | eq_ref | uid           | uid     | 4       | bill.u.id   |     1 |                          |
|  1 | PRIMARY            | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL        |  2239 |                          |
|  2 | DERIVED            | e1         | ALL    | NULL          | NULL    | NULL    | NULL        |  2288 | Using where              |
|  2 | DERIVED            | e2         | eq_ref | PRIMARY       | PRIMARY | 4       | bill.e1.mid |     1 |                          |
|  3 | DEPENDENT SUBQUERY | e3         | ref    | PRIMARY,uid   | uid     | 4       | bill.e1.uid |     1 | Using where; Using index |
+----+--------------------+------------+--------+---------------+---------+---------+-------------+-------+--------------------------+

Upvotes: 1

Views: 146

Answers (4)

Neville Kuyt
Neville Kuyt

Reputation: 29649

Consider adding an index on the column MID and UID on employees_log - the explain suggests that this join is not using an index.

Like so: create index compound on employees_log (mid, uid)

Upvotes: 0

chucknelson
chucknelson

Reputation: 2336

With your goal of trying to join to the log for the last employee for that user in the log table (based on the key at least), maybe just try a = <subquery> instead of a NOT EXISTS?

SELECT e2.full_name, e1.uid, e1.mid AS moid
FROM employees_log e1
LEFT JOIN employees e2 ON e1.mid = e2.id
WHERE e1.id = (
  SELECT MAX(e3.id)
  FROM employees_log e3
  WHERE e1.uid = e3.uid
)

Upvotes: 0

Sundar G
Sundar G

Reputation: 1069

If you are creating a new MySQL table you can specify a column to index by using the INDEX term.Indexes are something extra that you can enable on your MySQL tables to increase performance http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm
http://www.tutorialspoint.com/mysql/mysql-indexes.htm view this it gives you much idea..

Upvotes: 1

sd1sd1
sd1sd1

Reputation: 1048

first of all, i think you have to expalin to yourself why using int and bigint. do you really expect so much data? try using smallint or mediumint, they need less memory and are much faster. if you use the mediumint and smallint as unsigned, they can have a pretty large value, take a look at: http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

second, you need to combine some field to one key:

ALTER TABLE  `employees_log ` ADD INDEX (  `uid` ,  `id` ) ;

Upvotes: 1

Related Questions