php - Classic issues of MYSQL index optimization,

Question

The question is as follows:

< /p>

Like this

< /p>

The table structure is as follows:

CREATE TABLE `lmx_app_category` (
  `id` int(11) NOT NULL DEFAULT '0' COMMENT 'Category number',
  `pid` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Type of classification (currently two: 1: application, 2: game)',
  `name` varchar(50) NOT NULL COMMENT 'Category name',
  PRIMARY KEY (`cat_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Application classification table';

CREATE TABLE `lmx_apps` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'app number',
  `cat_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Category number',
  `name` varchar(100) NOT NULL COMMENT 'app name',
  `year` char(5) NOT NULL DEFAULT '0' COMMENT 'Year',
  `down_count` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Downloads',
  `hit_count` bigint(20) DEFAULT '0' COMMENT 'search volume',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

I have now created two joint indexes:

< /p>

But there are several problems with these two indexes. If I click Search All, the sorting will not be indexed. How to solve this problem of selecting all without indexing when searching for categories?

The sql for indexing is as follows:

 -- EXPLAIN
 SELECT a.id,a.cat_id,a.`name`,a.down_count,b.`name` FROM `lmx_apps` a

 INNER JOIN `lmx_app_category` `b`
 
 ON `a`.`cat_id`=`b`.`id`

  WHERE
   a.cat_id = 103
    

   ORDER BY a.down_count DESC
    
 LIMIT 10,20
 
 

The sql without indexing is as follows

 SELECT a.id,a.cat_id,a.`name`,a.down_count,b.`name` FROM `lmx_apps` a

 INNER JOIN `lmx_app_category` `b`
 
 ON `a`.`cat_id`=`b`.`id`

  -- WHERE
  -- a.cat_id IN (SELECT cat_id FROM lmx_app_category WHERE orgame = 1)
  -- When there is no cat_id condition or the condition is in

 ORDER BY a.down_count DESC
    
 LIMIT 10,20

Answer 1

Now I think of another plan, which is

Delete the cat_id field of the application table and the index created by cat_id

Create a relationship table between the classification table (lmx_app_category) and the application table (lmx_apps),

The fields are id, app_id, cat_id

Create an index on this related table. I don’t know what this solution looks like