1

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?

7
  • common man , you need to provide more information on this. what query are you doing ? what is size of table ? structure of table ? how many times will this table use ? – simplifiedDB Jun 27 '17 at 5:40
  • can you please paste output of 'show create table table_name' . Can you please post explain plan of your query by doing EXPLAIN your_query – simplifiedDB Jun 27 '17 at 6:05
  • I am really interested on how you concluded that "a query of cat_3 will NOT use the index." – ypercubeᵀᴹ Jun 27 '17 at 7:06
  • @ypercubeᵀᴹ because I read that if I index a 3 column ex(col1, col2, col3), a query of col3 will not use the index. – Dumb Question Jun 27 '17 at 7:09
  • 1
    @DumbQuestion then why would an index on cat_2 use it? What's the difference between the second and third field? A cutoff at an arbitrary ordinal position? – Tom V Jun 27 '17 at 11:45
1

I assume by "use the index", you mean that the columns are referenced in a WHERE clause?

Your understanding of indexes is quite far off.

INDEX(`title`, `cat_1`, `cat_2`, `cat_3`, `date_posted`)

Cannot be used if you don't include some reference to title. And some references to title won't work, such as:

WHERE `title` LIKE '%title%'

Certain other constructs work only for the last used column: "ranges" such as these

BETWEEN 22 AND 33
LIKE 'foo%'    -- note trailing wildcard, but not leading wildcard

In addition to the links already given, I proffer this one: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

3
  • +1 thanks! yes, you are right. thanks for the link, I saved it. Btw, I used the Full Text Search Index on my basic search instead of %text% wildcard. – Dumb Question Jul 6 '17 at 2:25
  • Hi, I have a question, does DELETE statement uses an index? Thank you. – Dumb Question Jul 6 '17 at 2:35
  • 1
    DELETE and UPDATE also use indexes when appropriate. Think of it this way -- they have to locate the row before deleting or updating it. Locating the row is essentially a SELECT. – Rick James Jul 6 '17 at 3:05
1

SQL might use the index for any of the queries above, but it might not use them either. How SQL picks an index is more complex than what you have stated. For example SQL might decide that it's quicker to do a table scan than to do a key lookup and so ignore the index even if your criteria is just TITLE.

If you look at the query plan when you specify the following, if it uses the index then you'll have an Index Scan instead of an index Seek:

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.

If it does use your index then it's because SQL has decided that it's still quicker to scan the index rather than scan the clustered key.

4
  • Then, how can I check if the index is working? Insert a 500 dummy data? – Dumb Question Jun 27 '17 at 7:11
  • Thanks, I edited my question, I added all of my queries related to the index I wanted. – Dumb Question Jun 27 '17 at 7:35
  • 1
    Check the query plan, you'll see an index scan instead of an index seek. For 2000 rows it may perform well enough to not matter. – Greg Jun 27 '17 at 21:12
  • Hi, I researched 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? – Dumb Question Jun 28 '17 at 1:38
1

My full conclusion here is that you should spend some time learning about indexes on BTREE data structures. The implementation in MySQL isn't very complicated and the sharp edges are evidently unknown. I've added some resources to my original answer and if you're serious about the performance of this and future applications you write/optimise with MySQL you should do your homework. Feeding you the fish here will not help you longer term.

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

Only filtering, aggregating or ordering involving the left most column will be valid for this index.

i.e.

SELECT {cols} FROM {table_name} WHERE title = something and cat_1 BETWEEN value AND value;

Or

SELECT {cols} FROM {table_name} WHERE cat_1 in (value, value, value) ORDER BY title

If you want to learn more on MySQL indexing check out resources such as

https://www.percona.com/files/presentations/WEBINAR-tools-and-techniques-for-index-design.pdf

Indexing in general is well detailed by Markus Winand

http://use-the-index-luke.com

Your queries using

... LIKE '%value%' ...

are unable to use indexes because of the wild carding. You can switch to using FULLTEXT matching. The LIKE pattern is not scalable and will cause full table scans all the time.

2
  • Thanks, I edited my question, I added all of my queries related to the index I wanted. – Dumb Question Jun 27 '17 at 7:35
  • Hi, I researched 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? – Dumb Question Jun 28 '17 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.