CODEWITHSUNDEEP
mysqloptimization
Kuba
Kuba

Reputation: 125

Optimizing a large MySQL query

Today I was told by my hosting provider that my database must be blocked immediately. I have a query that causes huge load on the server. Do you know how can I optimize my query just to make the server less loaded?

SELECT `users`.`id`,
       `why_me`, 
       `created`,
       `thumbnail`,
       `rating_date`,
       CONCAT(first_name, ' ', last_name) AS name,
       SUBSTRING(why_me, 1, 27) as subwhy,
       COUNT(`rating_date`) AS total,
       MAX(`rating_date`) AS maxrate
  FROM (`user_profiles`)
  LEFT OUTER JOIN `rates` ON `user_profiles`.`id`=`rates`.`user_id`
  JOIN `users` ON `user_profiles`.`user_id`=`users`.`id`
  WHERE `users`.`activated` =  '1'
      AND `last_name` != ""
      AND `first_name` != ""
      AND  concat_ws(' ', first_name, last_name) COLLATE UTF8_GENERAL_CI  LIKE '%%'
  GROUP BY `user_profiles`.`user_id`
  ORDER BY `total` desc

I'd appreciate any help from you. Thanks!

Upvotes: 2

Views: 108

Answers (1)

Zsolt Szilagyi
Zsolt Szilagyi

Reputation: 5026

AND  concat_ws(' ', first_name, last_name) COLLATE UTF8_GENERAL_CI  LIKE '%%'

has no effect but is expensive (long running). It scans all the rows one-by-one without returning anything usefull. Remove!

Log in into phpmyadmin and run this query with "EXPLAIN" prepended, i.e. "EXPLAIN SELECT users...". Post the result. It will show us if SQL can directly adress the required rows, or if it searching them one-by-one. Look out for the row used keys: if it´s empty, you need additioanl indeces:

1) Make sure you have an index on every column used for the joins, i.e. rates.user_idand user_profiles.user_id.

2) Make sure you have indeces on everything used for the where part with a high cardinality, i.e. first_name and last_name.

3) Even better: Create an index over 3 columns: activated,first_name,last_name

4) The GROUP and ORDER BY are causing the buildt of a temporary table. Can you port some of this logic into PHP?

5) Cache the result. That´s nothing to be done in real time!

Upvotes: 1

Related Questions