如何在SQL中创建索引以提高性能 [英] How to create index in SQL to increase performance

问题描述

我在数据库表中有大约 200,000 行.当我执行搜索查询时，大约需要 4-5 秒才能在下一页显示结果.我希望执行速度应该很快并且结果应该在 2 秒内加载.我的表中有大约 16 列.

I have around 200,000 rows in database table. When I execute my search query, it's taking around 4-5 seconds to give me results in next page. I want that execution should be fast and results should be loaded under 2 seconds. I have around 16 columns in my table.

以下是我创建表的查询

Create table xml(
PID int not null,
Percentdisc int not null,
name varchar(100) not null,
brand varchar(30) not null,
store varchar(30) not null,
price int not null,
category varchar(20) not null,
url1 varchar(300) not null,
emavail varchar(100) not null,
dtime varchar(100) not null,
stock varchar(30) not null,
description varchar(200) not null,
avail varchar(20) not null,
tags varchar(30) not null,
dprice int not null,
url2 varchar(300),
url3 varchar(300),
sid int primary key auto_increment);

选择我正在使用的查询

select * from feed where (name like '%Baby%' And NAME like '%Bassinet%')

我不太了解为数据库建立索引以提高性能.请指导我使用什么索引.

I dont have much knowledge of indexing the database, to increase performance. Please guide me what index to use.

推荐答案

索引无济于事.LIKE 是非 sargable 运算符.http://en.wikipedia.org/wiki/Sargable

Indexes aren't going to help. LIKE is a non sargable operator. http://en.wikipedia.org/wiki/Sargable

这篇关于如何在SQL中创建索引以提高性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！