i have table with 100 000 000 rows so large. Structure of table
我有100万行这么大的表。表的结构
id int INDEX(not primary not unique just index)
lang_index varchar(5) INDEX
name varchar(255) INDEX
enam varchar(255) INDEX
Ok. i do query
好。我查询
1 Query
1查询
"SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'"
Speed is ok for this large table. around 0.02 sec.
这张大桌的速度还可以。大约0.02秒。
i try 2 Query
我尝试2查询
"SELECT name FROM table WHERE lang_index='en' AND (name LIKE 'myname%' OR enam LIKE 'myname%')"
Very very slow around 230 sec!!!
在230秒左右非常慢!
then i try this 3 Query
然后我尝试这3个查询
"SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'"
Speed is fantastic. around 0.02 sec.
速度太棒了。大约0.02秒。
Then i explode my 2nd query for two queries (1 and 3 query) its faster. around 0.04 sec but it not simply.
然后我更快地爆炸我的第二个查询两个查询(1和3查询)。大约0.04秒但不简单。
Why my query is slow? Two queries much faster than one. I need do this "SELECT name FROM table WHERE lang_index='en' AND (name LIKE 'myname%' OR enam LIKE 'myname%')"
How i can make it faster?
为什么我的查询很慢?两个查询比一个查询快得多。我需要这样做“SELECT name FROM table WHERE lang_index ='en'AND(name LIKE'myname%'或enam LIKE'myname%')”我怎样才能让它更快?
1 个解决方案
#1
4
The OR
keyword drives MySQL's optimizer crazy.
OR关键字让MySQL的优化器变得疯狂。
You might try something like this.
你可能会尝试这样的事情。
SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'
UNION
SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'
Or you might consider FULLTEXT searching if you can (if your table has MyISAM for access).
或者您可以考虑使用FULLTEXT搜索(如果您的表具有MyISAM用于访问)。
EDIT* It's hard to know exactly what's going on with these optimization things. Can you try this? This will see whether the language selection is fouling you up.
编辑*很难确切知道这些优化事情究竟发生了什么。你能试试吗?这将看到语言选择是否会影响你。
SELECT name
FROM table
WHERE (name LIKE 'myname%' OR enam LIKE 'myname%')
Can you try this?
你能试试吗?
SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'
UNION ALL
SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'
It won't give a perfect result -- it will have duplicate name items -- but it will skip a DISTINCT
deduplicating step in your query.
它不会给出完美的结果 - 它将具有重复的名称项 - 但它将跳过查询中的DISTINCT重复数据删除步骤。
You might also try this.
你也可以试试这个。
SELECT name
FROM table
WHERE lang_index='en'
AND id IN (
SELECT id from table
WHERE (name LIKE 'myname%' OR enam LIKE 'myname%'))
#1
4
The OR
keyword drives MySQL's optimizer crazy.
OR关键字让MySQL的优化器变得疯狂。
You might try something like this.
你可能会尝试这样的事情。
SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'
UNION
SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'
Or you might consider FULLTEXT searching if you can (if your table has MyISAM for access).
或者您可以考虑使用FULLTEXT搜索(如果您的表具有MyISAM用于访问)。
EDIT* It's hard to know exactly what's going on with these optimization things. Can you try this? This will see whether the language selection is fouling you up.
编辑*很难确切知道这些优化事情究竟发生了什么。你能试试吗?这将看到语言选择是否会影响你。
SELECT name
FROM table
WHERE (name LIKE 'myname%' OR enam LIKE 'myname%')
Can you try this?
你能试试吗?
SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'
UNION ALL
SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'
It won't give a perfect result -- it will have duplicate name items -- but it will skip a DISTINCT
deduplicating step in your query.
它不会给出完美的结果 - 它将具有重复的名称项 - 但它将跳过查询中的DISTINCT重复数据删除步骤。
You might also try this.
你也可以试试这个。
SELECT name
FROM table
WHERE lang_index='en'
AND id IN (
SELECT id from table
WHERE (name LIKE 'myname%' OR enam LIKE 'myname%'))