Mysql – Will this indexing work as I think it will

I am a little confused about the indexing I am going to do.

First, I am using a 4-column index, like this:

Index Name – advanced_query

Columns will be used in the index – title, category 1, category 2, category 3

The Indexing Code

ALTER TABLE table_name ADD INDEX advanced_query (`title`, `cat_1`, `cat_2`, `cat_3`, `date_posted`)

Okay, so this is how (from what I understand) it will work:

• a query of title will use the index.
• a query of cat_1 will use the index.
• a query of cat_2 will use the index.
• a query of cat_3 will NOT use the index. So I will create a different index for it.
• a query of title, cat_1 will use the index.
• a query of title, cat_1, cat_2 will use the index.
• a query of title, cat_1, cat_2, cat_3 will use the index.
• a query of title, cat_1, cat_3 will use the index.
• a query of title, cat_2 will use the index.
• a query of title, cat_2, cat_3 will use the index.
• a query of title, cat_3 will use the index.
• a query of cat_1, cat_2 will use the index.
• a query of cat_1, cat_2, cat_3 will use the index.
• a query of cat_1, cat_2 will use the index.
• a query of cat_1, cat_3 will use the index.

TL;DR

So in this index, only a query of cat_3 will not benefit from it, right? Thanks!

Edit

What query are you doing ? searching a post (it's title and 3 different categories)

What is size of table ? Less than 2 thousand rows

Structure of table ?

CREATE TABLE `post_lists` (
 `id` int(100) NOT NULL AUTO_INCREMENT,
 `users_id` varchar(100) NOT NULL,
 `code` varchar(255) NOT NULL,
 `date_posted` datetime NOT NULL,
 `date_updated` datetime NOT NULL,
 `title` varchar(255) NOT NULL,
 `cat_1` varchar(255) NOT NULL,
 `cat_3_code` varchar(255) NOT NULL,
 `details` varchar(10000) NOT NULL,
 `cat_2` varchar(255) NOT NULL,
 `cat_3` varchar(255) NOT NULL,
 UNIQUE KEY `id` (`id`),
 KEY `date_posted` (`date_posted`),
 KEY `code` (`urlcode`),
 KEY `users_id_date_posted` (`users_id`,`date_posted`),
 KEY `title_date_posted` (`title`,`date_posted`),
 KEY `cat_1_date_posted` (`cat_1`,`date_posted`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1

How many times will this table use ? Most of the time. This is the advanced search function so not just frequently as the basic search is.

Edit #2

Sorry! I forgot to add the date_posted.

This is how I actually will use the index.

Example Table

title | cat_1 | cat_2 | cat_3 | date_posted

My queries are simple:

1. title

   SELECT * FROM tbl_name WHERE title LIKE %title% ORDER BY date_posted DESC

2. title + cat_1

   SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' ORDER BY date_posted DESC

3. title + cat_1 + cat_2

   SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' AND cat_2 = 'cat_2' ORDER BY date_posted DESC

4. title + cat_1 + cat_2 + cat_3

   SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC

5. title + cat_1 + cat_3

   SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' and cat_3 = 'cat_3' ORDER BY date_posted DESC

6. title + cat_2

   SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_2 = 'cat_2' ORDER BY date_posted DESC

7. title + cat_2 + cat_3

   SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC

8. title + cat_3

   SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_3 = 'cat_3' ORDER BY date_posted DESC

9. cat_1

   SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' ORDER BY date_posted DESC

10. cat_1 + cat_2

    SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_2 = 'cat_2' ORDER BY date_posted DESC

11. cat_1 + cat_2 + cat_3

    SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC

12. cat_1 + cat_3

    SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_3 = 'cat_3' ORDER BY date_posted DESC

13. cat_2

    SELECT * FROM tbl_name WHERE cat_2 = 'cat_2' ORDER BY date_posted DESC

14. cat_2 + cat_3

    SELECT * FROM tbl_name WHERE cat_2 = 'cat_2' ORDER BY date_posted DESC

15. cat_3

    SELECT * FROM tbl_name WHERE cat_3 = 'cat_3' ORDER BY date_posted DESC

How can I query this?

Edit

Hi, I read and searched about Full Text Search, and I am thinking to use it (in basic search) instead of LIKE %wildcard% and applying it to title and details, my problem is I want them to sort ORDER BY date_posted DESC, so should I add date_posted in Full Text Search or create a separate index?