CODEWITHSUNDEEP
mysqlindexingscalability
DonJoe
DonJoe

Reputation: 1763

How should I use indexing in a very big table

Let's say I have the following table, which can contain 100M + rows.

Table example

id           user_id          week    content                    created
========================================================================
1            100022312        1     <data to be searched>    <timestamp>
2            102232123        1     <data to be searched>    <timestamp>
...
10.000.000   126387126        1     <data to be searched>    <timestamp>
10.000.001   100022312        2     <data to be searched>    <timestamp>
10.000.002   102232123        2     <data to be searched>    <timestamp>
...
20.000.000   126387126        2     <data to be searched>    <timestamp>
....

week 3, 4, 5, 6 .....

I will query the table like this:

SELECT * FROM table WHERE week='2' AND content LIKE %word%

My question:

Could I use indexes to make this query run faster? How?

Is there a way to index a row? I mean, I would like to index each first row of each week, so that the next time I select, it first looks at the indexes, matches the week number, then it searches in that batch. This would mean nr indexes == nr weeks.

Data insertion does not matter. Also I don't need any sorting.

I am using MyISAM.

Upvotes: 0

Views: 396

Answers (2)

Rick James
Rick James

Reputation: 142278

Switch to a FULLTEXT index on content.

Upvotes: 1

Barmar
Barmar

Reputation: 780889

Use a multi-column index on the week and content columns:

ALTER TABLE yourTable ADD INDEX (week, content);

An index on a string column won't help if you're searching for text in the middle of it. But it will help with exact and prefix matches, will it will be useful if you do content = 'word' or content LIKE 'word%'.

Upvotes: 2

Related Questions