MySQL慢查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL慢查询,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5150字,纯文字阅读大概需要8分钟。
内容图文
![MySQL慢查询](/upload/InfoBanner/zyjiaocheng/886/6fa94f652c824adab323a8b4cc6650c4.jpg)
SELECT
items.item_id, items.category_id, items.title, items.description, items.quality,
items.type, items.status, items.price, items.posted, items.modified,
zip_code.state_prefix, zip_code.city, books.isbn13, books.isbn10, books.authors,
books.publisher
FROM
(
(
items
LEFT JOIN bookitems ON items.item_id = bookitems.item_id
)
LEFT JOIN books ON books.isbn13 = bookitems.isbn13
)
LEFT JOIN zip_code ON zip_code.zip_code = items.item_zip
WHERE items.rid = $rid`
我正在运行此查询以获取用户项目及其位置的列表.邮政编码表有超过4万条记录,这可能是问题所在.目前最多可能需要15秒才能返回大约20个项目的列表!我该怎么做才能使查询效率更高?
更新:以下是相关表的表创建代码.抱歉,格式化!
CREATE TABLE `bookitems` (
`bookitem_id` int(10) unsigned NOT NULL auto_increment COMMENT 'BookItem ID',
`item_id` int(10) unsigned NOT NULL default '0' COMMENT 'Item ID',
`isbn13` varchar(13) NOT NULL default '' COMMENT 'Book ISBN13',
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Date of Last Modification',
PRIMARY KEY (`bookitem_id`),
UNIQUE KEY `item_id` (`item_id`),
KEY `fk_bookitems_isbn13` (`isbn13`),
CONSTRAINT `fk_bookitems_isbn13` FOREIGN KEY (`isbn13`) REFERENCES `books` (`isbn13`),
CONSTRAINT `fk_bookitems_item_id` FOREIGN KEY (`item_id`) REFERENCES `items` (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=latin1;
CREATE TABLE `books` (
`isbn13` varchar(13) NOT NULL default '' COMMENT 'Book ISBN13 (pk)',
`isbn10` varchar(10) NOT NULL default '' COMMENT 'Book ISBN10 (u)',
`title` text NOT NULL COMMENT 'Book Title',
`title_long` text NOT NULL,
`authors` text NOT NULL COMMENT 'Book Authors',
`publisher` text NOT NULL COMMENT 'ISBNdb publisher_text',
PRIMARY KEY (`isbn13`),
UNIQUE KEY `isbn10` (`isbn10`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `items` (
`item_id` int(10) unsigned NOT NULL auto_increment COMMENT 'Item ID',
`rid` int(10) unsigned NOT NULL default '0' COMMENT 'Item Owner User ID',
`category_id` int(10) unsigned NOT NULL default '0' COMMENT 'Item Category ID',
`title` tinytext NOT NULL COMMENT 'Item Title',
`description` text NOT NULL COMMENT 'Item Description',
`quality` enum('0','1','2','3','4','5') NOT NULL default '0' COMMENT 'Item Quality',
`type` enum('forsale','wanted','pending') NOT NULL default 'pending' COMMENT 'Item Status',
`price` int(6) unsigned NOT NULL default '0' COMMENT 'Price',
`posted` datetime NOT NULL default '0000-00-00 00:00:00' COMMENT 'Date of Listing',
`modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'Date of Last Modification',
`status` enum('sold','found','flagged','removed','active','expired') NOT NULL default 'active',
`item_zip` int(5) unsigned zerofill NOT NULL default '00000',
PRIMARY KEY (`item_id`),
KEY `fk_items_rid` (`rid`),
KEY `fk_items_category_id` (`category_id`),
CONSTRAINT `fk_items_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`),
CONSTRAINT `fk_items_rid` FOREIGN KEY (`rid`) REFERENCES `users` (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1;
CREATE TABLE `users` (
`rid` int(10) unsigned NOT NULL auto_increment COMMENT 'User ID',
`fid` int(10) unsigned NOT NULL default '0' COMMENT 'Facebook User ID',
`role_id` int(10) unsigned NOT NULL default '4',
`zip` int(5) unsigned zerofill NOT NULL default '00000' COMMENT 'Zip Code',
`joined` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'INSERT Timestamp',
`email` varchar(255) NOT NULL default '',
`notes` varchar(255) NOT NULL default '',
PRIMARY KEY (`rid`),
UNIQUE KEY `fid` (`fid`),
KEY `fk_users_role` (`role_id`),
CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1013 DEFAULT CHARSET=latin1;
CREATE TABLE `zip_code` (
`id` int(11) unsigned NOT NULL auto_increment,
`zip_code` varchar(5) character set utf8 collate utf8_bin NOT NULL,
`city` varchar(50) character set utf8 collate utf8_bin default NULL,
`county` varchar(50) character set utf8 collate utf8_bin default NULL,
`state_name` varchar(50) character set utf8 collate utf8_bin default NULL,
`state_prefix` varchar(2) character set utf8 collate utf8_bin default NULL,
`area_code` varchar(3) character set utf8 collate utf8_bin default NULL,
`time_zone` varchar(50) character set utf8 collate utf8_bin default NULL,
`lat` float NOT NULL,
`lon` float NOT NULL,
`search_string` varchar(52) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `zip_code` (`zip_code`)
) ENGINE=MyISAM AUTO_INCREMENT=42625 DEFAULT CHARSET=utf8;
解决方法:
SELECT items.item_id,
items.category_id,
items.title,
items.description,
items.quality,
items.TYPE,
items.status,
items.price,
items.posted,
items.modified,
zip_code.state_prefix,
zip_code.city,
books.isbn13,
books.isbn10,
books.authors,
books.publisher
FROM items
LEFT JOIN
bookitems
ON bookitems.item_id = items.item_id
LEFT JOIN
books
ON books.isbn13 = bookitems.isbn13
LEFT JOIN
zip_code
ON zip_code.zip_code = items.item_zip
WHERE items.rid = $rid
创建以下索引:
items (rid)
bookitems (item_id)
books (isbn13)
zip_code (zip_code)
内容总结
以上是互联网集市为您收集整理的MySQL慢查询全部内容,希望文章能够帮你解决MySQL慢查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。