MYSQL Query speed is very slow. Is there a problem with my table structure? please take a look.

MYSQL Query speed is very slow. Is there a problem with my table structure? please take a look. Table 1 is a novel table:
Create table if not exists 'book '(
'Bid' int (6) unsigned not null AUTO_INCREMENT,
'Siteid' smallint (2) not null default '0', multi-site ID
'Catid' smallint (6) not null, topic ID
'Title' char (80) not null, title
'Word' char (20) not null default '0', word count statistics
'Content' text not null, introduction
'Inputtime' int (11) not null, input time
'Updatetime' int (11) not null, update time
'Username' varchar (40) not null, user name
'Author' varchar (100) not null, author
'Letter 'char (5) not null, first letter
'Thumb' varchar (200) not null, thumbnail
'URL' varchar (200) not null, static address
'Keyword' varchar (200) not null, keyword
'Passed' tinyint (1) not null default '0', serialization
'Status' tinyint (2) not null default '99', review
'Pod' tinyint (1) not null default '0', recommended ID
Primary key ('bid '),
KEY 'siteid' ('siteid', 'passed ', 'catid ')
) ENGINE = MyISAM default charset = utf8;

Table 2: chapter table
Create table if not exists 'Book _ capital '(
'CID' int (8) unsigned not null AUTO_INCREMENT,
'Bid' int (6) unsigned not null, book ID
'Storder' int (8) not null default '0', sort
'Title' varchar (180) not null, chapter title
'Content' text not null, chapter content
'Updatetime' int (11) not null, update time
'URL' varchar (200) not null, chapter address
Primary key ('CID ')
ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 38065;


I don't know what the problem is. the query speed is very slow. Please check whether the table is created incorrectly.

Reply to discussion (solution)

SQL command problems

Public function lists ($ data ){
$ Siteid = $ data ['siteid']? Intval ($ data ['siteid']): get_siteid ();
$ Catid = intval ($ data ['catid']);
$ Passed = $ data ['passed '] = ''? "3": $ data ['passed '];
$ Posid = intval ($ data ['posid']);
$ Catidin = $ data ['catidin'];
// Echo $ data ['letter'];
// Echo ord ($ data ['letter']);
If (ord ($ data ['letter '])> 60 ){
$ Letter = $ data ['letter'];
}
// $ Letter = $ data ['letter '] = 0? "": $ Data ['letter'];
If (isset ($ data ['where']) {$ SQL = "and". $ data ['where'];}
$ Thumb = intval ($ data ['thumb'])? "AND thumb! = ''":'';
If ($ siteid) $ where. = "'siteid' = '". $ siteid ."'";
If ($ passed = 0 or $ passed = 1) $ where. = "and 'passed '='". $ passed ."'";
If ($ posid) $ where. = "and 'posid '='". $ posid ."'";
If ($ catidin) $ where. = "and catid in ('". $ catidin ."')";
If ($ catid) $ where. = "and 'catid' = '". $ catid ."'";
If ($ letter) $ where. = "and 'letter '='". $ letter ."'";
$ Where. = 'AND 'status' = \ '99 \ ''. $ SQL. $ thumb;
$ Order = $ data ['order'];
Return $ this-> db-> select ($ where, '*', $ data ['limit'], $ order );
}


This is the query tag code. Is there a problem here.

Whether there is a problem. you need to see the complete query command to know.

The first is the query of hotspot content, which is the query command associated with the click table:
Select. bid,. title,. author,. content,. updatetime,. catid,. url,. posid, B. bid, B. hits, B. hits from book a, book_hits B where. bid = B. bid and. posid = 2 order by hits desc LIMIT 1

Click table:
Create table if not exists 'Book _ hits '(
'Catid' int (6) not null,
'Bid' int (6) unsigned not null,
'Hits 'Int (6) not null default '0 ',
'Day' int (6) DEFAULT '0 ',
'Week' int (6) DEFAULT '0 ',
'Month' int (6) DEFAULT '0 ',
'Updatetime' int (11) not null,
Primary key ('bid ')
) ENGINE = MyISAM default charset = utf8;


The second one is for a single table query: select * from book where 'siteid' = '1' and 'posid' = '1' and 'catid' = '22' AND 'status' = '99' AND thumb! = ''Order by bid desc limit 0, 10

These two types of queries are available on the entire page. It takes about 20 seconds to open the homepage.

Right? Large?

Add explain in front of your query statement in the queryer
For example
EXPLAIN select * from user where user = '20140901'
For more information, see
Http://www.blogjava.net/persister/archive/2008/10/27/236813.html

Desc select * from... Analyze the slow query and add the index properly

Add an index to the table based on the query requirements.