Full text indexes on big tables The 2019 Stack Overflow Developer Survey Results Are In ...
How did the audience guess the pentatonic scale in Bobby McFerrin's presentation?
How to grep and cut numbes from a file and sum them
How is simplicity better than precision and clarity in prose?
What's the point in a preamp?
How to copy the contents of all files with a certain name into a new file?
Why can't devices on different VLANs, but on the same subnet, communicate?
Why not take a picture of a closer black hole?
Python - Fishing Simulator
Take groceries in checked luggage
Mortgage adviser recommends a longer term than necessary combined with overpayments
What can I do if neighbor is blocking my solar panels intentionally?
Keeping a retro style to sci-fi spaceships?
Converting from Markdown-with-biblatex-commands to LaTeX
The following signatures were invalid: EXPKEYSIG 1397BC53640DB551
A pet rabbit called Belle
How did passengers keep warm on sail ships?
Finding the path in a graph from A to B then back to A with a minimum of shared edges
Is this wall load bearing? Blueprints and photos attached
Match Roman Numerals
The variadic template constructor of my class cannot modify my class members, why is that so?
Difference between "generating set" and free product?
Is there a writing software that you can sort scenes like slides in PowerPoint?
What do you call a plan that's an alternative plan in case your initial plan fails?
Why is superheterodyning better than direct conversion?
Full text indexes on big tables
The 2019 Stack Overflow Developer Survey Results Are In
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)SQL Server 2005: Full-text search catalog designSQL Server 2005: Full-text search space requirementsSQL Server 2005: Full-text search on a multi-lingual tableHow to design a table for storing news items?Full-text search in SQL Server on multiple tables failsKey Lookup and Full-text indexUpdate queries slower after enabling SQL Server Full-Text IndexWhy is full text index falling behind?one to many relationship DB structureFull text search in PostgreSQL with search term including unrelated hyphen and ending
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have a news website where I store my articles in a table that looks like that:
Id title data
Where data contains the content of the article and is the biggest column in the database by far. The table currently takes 10 GB.
I have an issue that queries that search by content on this table take extremely long time as it is done by like clauses instead of full text.
My team told me that there was a full text index on the column, but it was too big and gave them hard time, even crashing the database.
What is the best way to deal with this issue?
mysql index full-text-search
edited Feb 10 '16 at 11:09
Arulkumar
860417
asked Feb 10 '16 at 8:15
user69153user69153
8110
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a news website where I store my articles in a table that looks like that:
Id title data
Where data contains the content of the article and is the biggest column in the database by far. The table currently takes 10 GB.
I have an issue that queries that search by content on this table take extremely long time as it is done by like clauses instead of full text.
My team told me that there was a full text index on the column, but it was too big and gave them hard time, even crashing the database.
What is the best way to deal with this issue?
mysql index full-text-search
edited Feb 10 '16 at 11:09
Arulkumar
860417
asked Feb 10 '16 at 8:15
user69153user69153
8110
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
What version of MySQL? What engine does the table use? It would be better if you added theSHOW CREATE TABLE tablename;output in the question.
– ypercubeᵀᴹ
Feb 10 '16 at 11:10
A full-text index crashing the "database", sounds weird by the way. What kind of crashes did happen?
– ypercubeᵀᴹ
Feb 10 '16 at 11:11
5.6, and i think the index was too big. We talk about a 10 gb table, its innodb, the field is medium text
– user69153
Feb 10 '16 at 15:34
@user69153 still the server is not supposed to crash in such case unless it is misconfigured (for example to use more memory than available) or you hit a bug
– jkavalik
Feb 11 '16 at 6:35
Tell your team that they shoyld let you create the FULTEXT index on a recent 5.6 version, or you will be forced to install java for Elasticsearch and they will have 2 problems :-)
– jynus
Nov 6 '18 at 20:15
add a comment |
I have a news website where I store my articles in a table that looks like that:
Id title data
Where data contains the content of the article and is the biggest column in the database by far. The table currently takes 10 GB.
I have an issue that queries that search by content on this table take extremely long time as it is done by like clauses instead of full text.
My team told me that there was a full text index on the column, but it was too big and gave them hard time, even crashing the database.
What is the best way to deal with this issue?
mysql index full-text-search
edited Feb 10 '16 at 11:09
Arulkumar
860417
asked Feb 10 '16 at 8:15
user69153user69153
8110
I have a news website where I store my articles in a table that looks like that:
Id title data
Where data contains the content of the article and is the biggest column in the database by far. The table currently takes 10 GB.
I have an issue that queries that search by content on this table take extremely long time as it is done by like clauses instead of full text.
My team told me that there was a full text index on the column, but it was too big and gave them hard time, even crashing the database.
What is the best way to deal with this issue?
mysql index full-text-search
mysql index full-text-search
edited Feb 10 '16 at 11:09
Arulkumar
860417
asked Feb 10 '16 at 8:15
user69153user69153
8110
edited Feb 10 '16 at 11:09
Arulkumar
860417
asked Feb 10 '16 at 8:15
user69153user69153
8110
edited Feb 10 '16 at 11:09
Arulkumar
860417
edited Feb 10 '16 at 11:09
Arulkumar
860417
edited Feb 10 '16 at 11:09
Arulkumar
860417
860417
asked Feb 10 '16 at 8:15
user69153user69153
8110
asked Feb 10 '16 at 8:15
user69153user69153
8110
asked Feb 10 '16 at 8:15
user69153user69153
8110
8110
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 3 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
1
What version of MySQL? What engine does the table use? It would be better if you added theSHOW CREATE TABLE tablename;output in the question.
– ypercubeᵀᴹ
Feb 10 '16 at 11:10
A full-text index crashing the "database", sounds weird by the way. What kind of crashes did happen?
– ypercubeᵀᴹ
Feb 10 '16 at 11:11
5.6, and i think the index was too big. We talk about a 10 gb table, its innodb, the field is medium text
– user69153
Feb 10 '16 at 15:34
@user69153 still the server is not supposed to crash in such case unless it is misconfigured (for example to use more memory than available) or you hit a bug
– jkavalik
Feb 11 '16 at 6:35
Tell your team that they shoyld let you create the FULTEXT index on a recent 5.6 version, or you will be forced to install java for Elasticsearch and they will have 2 problems :-)
– jynus
Nov 6 '18 at 20:15
add a comment |
1
What version of MySQL? What engine does the table use? It would be better if you added theSHOW CREATE TABLE tablename;output in the question.
– ypercubeᵀᴹ
Feb 10 '16 at 11:10
A full-text index crashing the "database", sounds weird by the way. What kind of crashes did happen?
– ypercubeᵀᴹ
Feb 10 '16 at 11:11
5.6, and i think the index was too big. We talk about a 10 gb table, its innodb, the field is medium text
– user69153
Feb 10 '16 at 15:34
@user69153 still the server is not supposed to crash in such case unless it is misconfigured (for example to use more memory than available) or you hit a bug
– jkavalik
Feb 11 '16 at 6:35
Tell your team that they shoyld let you create the FULTEXT index on a recent 5.6 version, or you will be forced to install java for Elasticsearch and they will have 2 problems :-)
– jynus
Nov 6 '18 at 20:15
1
1
What version of MySQL? What engine does the table use? It would be better if you added the
SHOW CREATE TABLE tablename; output in the question.– ypercubeᵀᴹ
Feb 10 '16 at 11:10
What version of MySQL? What engine does the table use? It would be better if you added the
SHOW CREATE TABLE tablename; output in the question.– ypercubeᵀᴹ
Feb 10 '16 at 11:10
A full-text index crashing the "database", sounds weird by the way. What kind of crashes did happen?
– ypercubeᵀᴹ
Feb 10 '16 at 11:11
A full-text index crashing the "database", sounds weird by the way. What kind of crashes did happen?
– ypercubeᵀᴹ
Feb 10 '16 at 11:11
5.6, and i think the index was too big. We talk about a 10 gb table, its innodb, the field is medium text
– user69153
Feb 10 '16 at 15:34
5.6, and i think the index was too big. We talk about a 10 gb table, its innodb, the field is medium text
– user69153
Feb 10 '16 at 15:34
@user69153 still the server is not supposed to crash in such case unless it is misconfigured (for example to use more memory than available) or you hit a bug
– jkavalik
Feb 11 '16 at 6:35
@user69153 still the server is not supposed to crash in such case unless it is misconfigured (for example to use more memory than available) or you hit a bug
– jkavalik
Feb 11 '16 at 6:35
Tell your team that they shoyld let you create the FULTEXT index on a recent 5.6 version, or you will be forced to install java for Elasticsearch and they will have 2 problems :-)
– jynus
Nov 6 '18 at 20:15
Tell your team that they shoyld let you create the FULTEXT index on a recent 5.6 version, or you will be forced to install java for Elasticsearch and they will have 2 problems :-)
– jynus
Nov 6 '18 at 20:15
add a comment |
1 Answer
1
active
oldest
votes
Check firstly if the index fits into RAM.
If yes, check the table structure and the problematic query with DESCRIBE.
edited Mar 26 '16 at 21:55
marc_s
7,13853849
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f128816%2ffull-text-indexes-on-big-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Check firstly if the index fits into RAM.
If yes, check the table structure and the problematic query with DESCRIBE.
edited Mar 26 '16 at 21:55
marc_s
7,13853849
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
add a comment |
Check firstly if the index fits into RAM.
If yes, check the table structure and the problematic query with DESCRIBE.
edited Mar 26 '16 at 21:55
marc_s
7,13853849
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
add a comment |
Check firstly if the index fits into RAM.
If yes, check the table structure and the problematic query with DESCRIBE.
edited Mar 26 '16 at 21:55
marc_s
7,13853849
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
Check firstly if the index fits into RAM.
If yes, check the table structure and the problematic query with DESCRIBE.
edited Mar 26 '16 at 21:55
marc_s
7,13853849
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
edited Mar 26 '16 at 21:55
marc_s
7,13853849
edited Mar 26 '16 at 21:55
marc_s
7,13853849
edited Mar 26 '16 at 21:55
marc_s
7,13853849
7,13853849
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
answered Feb 10 '16 at 11:25
MvorisekMvorisek
218212
218212
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f128816%2ffull-text-indexes-on-big-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
What version of MySQL? What engine does the table use? It would be better if you added the
SHOW CREATE TABLE tablename;output in the question.– ypercubeᵀᴹ
Feb 10 '16 at 11:10
A full-text index crashing the "database", sounds weird by the way. What kind of crashes did happen?
– ypercubeᵀᴹ
Feb 10 '16 at 11:11
5.6, and i think the index was too big. We talk about a 10 gb table, its innodb, the field is medium text
– user69153
Feb 10 '16 at 15:34
@user69153 still the server is not supposed to crash in such case unless it is misconfigured (for example to use more memory than available) or you hit a bug
– jkavalik
Feb 11 '16 at 6:35
Tell your team that they shoyld let you create the FULTEXT index on a recent 5.6 version, or you will be forced to install java for Elasticsearch and they will have 2 problems :-)
– jynus
Nov 6 '18 at 20:15