Something wrong with indexes

Question

i have two tables :

First :

CREATE TABLE `dialog_projects` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`creator_user_id` INT(11) UNSIGNED NOT NULL,
`add_users_allowed` INT(1) NULL DEFAULT '1',
`dlg_name` VARCHAR(200) NOT NULL,
`dlg_date` DATETIME NOT NULL,
PRIMARY KEY (`id`),
INDEX `dialog_projects_creator_user_id_ind` (`creator_user_id`),
INDEX `dialog_projects_add_users_allowed_ind` (`add_users_allowed`),
INDEX `dialog_projects_dlg_date_ind` (`dlg_date`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=220094
/*!50100 PARTITION BY KEY (id)
PARTITIONS 10  */;

Second :

CREATE TABLE `dialog_users` (
`dialog_projects_id` INT(11) UNSIGNED NOT NULL,
`user_id` INT(11) UNSIGNED NOT NULL,
`num_new_msgs` INT(11) UNSIGNED NOT NULL,
`accepted` TINYINT(1) NULL DEFAULT '0',
`last_visit` DATETIME NOT NULL,
PRIMARY KEY (`dialog_projects_id`, `user_id`, `num_new_msgs`),
INDEX `dialog_projects_accepted_ind` (`accepted`),
INDEX `dialog_projects_last_visit_ind` (`last_visit`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
/*!50100 PARTITION BY HASH (dialog_projects_id + user_id + num_new_msgs)
PARTITIONS 10  */;

This query executes about 5,5 seconds, but without "order by du.num_new_msgs desc" - takes 0,005 seconds. How to increase speed ? What wrong?

#explain 
select SQL_NO_CACHE dp.id
from `dialog_users` as du 
left join `dialog_projects` as dp
 on (du.dialog_projects_id = dp.id) 
where dp.id > 300 and du.num_new_msgs > -1 
    and dp.add_users_allowed = 0 and du.user_id = 10990 

order by du.num_new_msgs desc, dp.id desc 
limit 10

Here's explain :

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "dp"    "ref"   "PRIMARY,dialog_projects_add_users_allowed_ind" "dialog_projects_add_users_allowed_ind" "5" "const" "100246"    "Using where; Using index; Using temporary; Using filesort"
"1" "SIMPLE"    "du"    "ref"   "PRIMARY"   "PRIMARY"   "8" "sport-event.dp.id,const"   "1" "Using where; Using index"

Thanks

Answer 1

Why does the performance decrease when you put an ORDER BY clause before your LIMIT clause? Because without ORDER BY the MySQL query engine just needs to return ten convenient rows, and then it can stop. With ORDER BY it needs to examine every row in the result set to find the ones you want.

Your query says this. I've reordered the clauses for clarity.

 where dp.id > 300 and dp.add_users_allowed = 0 
   and du.num_new_msgs > -1 and du.user_id = 10990 

You could try putting a compound index on dialog_projects on the (add_users_allowed, id) columns. That might help accelerate the lookup on that table.

You seem to be using partitioning for a relatively small table (300K rows). That is probably hammering your query performance. Most MySQL users don't even think about partititioning their tables until they get to row counts at least one hundred times as large as yours. And then they plan their queries very carefully, so most queries touch a limited number of partitions; hopefully just one.