MySQL Search Engine Optimization

Question

Here is the scenario 1.

I have a table called "items", inside the table there are 2 columns, e. d. item_id and item_name . I store my data this way: item_id | item_name

 Ss001 | Shirt1 Sb002 | Shirt2 Tb001 | TShirt1 Tm002 | TShirt2 

... etc., I store this way: the first letter is the code for the clothes, i.e. for a shirt, T for a t-shirt the second letter is the size, i.e. for small, m for medium and b for large Let's say in my table of items I received 10,000 items. I want to quickly find, I will say that I want to find a specific shirt, can I use:

Method1:

 SELECT * from items WHERE item_id LIKE Sb99; 

or should I do it like:

Method2:

 SELECT * from items WHERE item_id LIKE S*; 

* Save the result, then do a second size search, then a third identifier search. Like the concept of a hash table. What I want to achieve, instead of finding all the data, I want to first minimize the search by searching for the clothes code, following the size code, and then the id code. Which one is better in terms of speed in mysql. And which one is better in the long run. I want to reduce traffic and not break the database so often.

Thanks guys for solving my first scenario. But there is another scenario:

Scenario 2:

I use PHP and MySQL. Continue the story. If my table looks like this:

 user_id | username | items_collected U0001 | Alex | Ss001;Tm002 U0002 | Daniel | Tb001;Sb002 U0003 | Michael | ... U0004 | Thomas | ... 

I store items_collected in id form because one day each user can collect up to hundreds of items if I store as a string, i.e. Shirt1, pants2, ..., this would require a very large number of database spaces (imagine if we have 1000 users, and some elements are very long).

Would it be easier to maintain if I store in id form?

And if we say, I want to display the image, and the image name is the name of the element + jpg. How to do it? This is something like this:

$ result = Select items_collected from users, where userid = $ userid

Using php explode:

$ itemsCollected = explode ($ result, ";");

After that, matching each element in the element table, he would like to:

shirt1, pants2, etc.

Den, using a loop function, loop over each value and add ".jpg" to display the image?

Answer 1

The first method will be faster - but IMO is not the right way to do this. I agree with the tehwan about this.

I would recommend keeping item_id as it is, but add two additional fields, one for the code and one for the size, then you can do:

 select * from items where item_code = 'S' and item_size = 'm' 

With indexes, performance will be greatly increased, and you can easily match a range of sizes or codes.

 select * from items where item_code = 'S' and item_size IN ('m','s') 

Transfer db as follows:

 alter table items add column item_code varchar(1) default ''; alter table items add column item_size varchar(1) default ''; update items set item_code = SUBSTRING(item_id, 1, 1); update items set item_size = SUBSTRING(item_id, 2, 1); 

Changes to the code should be equally easy to add. Long-term benefits will be worth the effort.

---

For scenario 2, this is not an efficient way to store and retrieve data from a database. When used in this way, the database acts only as a storage mechanism, by encoding several data into fields that exclude the possibility of using the relational part of the database.

What you have to do in this case is to have another table, name it "items_collected". The circuit would be along the lines

 CREATE TABLE items_collected ( id int(11) NOT NULL auto_increment KEY, userid int(11) NOT NULL, item_code varchar(10) NOT NULL, FOREIGN KEY (`userid`) REFERENCES `user`(`id`), FOREIGN KEY (`itemcode`) REFERENCES `items`(`item_code`) ); 

Foreign keys ensure that referential integrity exists, it is important to have referential integrity .

Then for the example you are giving, you will have several entries.

  user_id | username | items_collected U0001 | Alex | Ss001 U0001 | Alex | Tm002 U0002 | Daniel | Sb002 U0002 | Daniel | Tb001 U0003 | Michael | ... U0004 | Thomas | ... 

Answer 2

The first optimization will split the identifier into three different fields: one for the type, one for the size, one for the current ending id (regardless of the final) If you really want to keep the current structure, immediately execute the result (option 1).

Answer 3

If you want to speed up the results, you must split the column into several columns, one for each property.

Step 2 - Create an index for each column. Remember that mysql uses only one index for each table for each query. Therefore, if you really need fast queries, and your queries are very different from these properties, you may need to create an index (type, size, end), (type, end, size), etc.

For example, a query with

 select * from items where type = s and size = s and ending = 001 

It can use an index (type, size, end), but:

 select * from items where size = s and ending = 001 

Impossible, because the index will be used only in order, so it needs a type, then size, and then the end. This is why you might need multiple indexes if you really want a quick search.

Another remark, as a rule, is not recommended to use * in queries, but to select only the necessary columns.

Answer 4

You need to have three columns for model , size and id and index them as follows:

 CREATE INDEX ix_1 ON (model, size, id) CREATE INDEX ix_2 ON (size, id) CREATE INDEX ix_3 ON (id, model) 

Then you can effectively search for any subset of parameters:

• model-size-id , model-size and model will be used ix_1 ;
• size-id and size will be used ix_2 ;
• model-id and id will be used ix_3

The pointer on your column, as of now, is equivalent to ix_1 , and you can use this index to search efficiently under the relevant conditions ( model-size-id , model-size and model ).

In fact, there is a specific access path named INDEX SKIN SCAN that can be used to search the non-first columns of a composite index, but MySQL does not support its AFAIK.

---

If you need to adhere to your current design, you need to index the field and use queries such as:

 WHERE item_id LIKE @model || '%' WHERE item_id LIKE @model || @size || '%' WHERE item_id = @model || @size || @id 

All these queries will use the index, if any.

No need to enter multiple queries.

Answer 5

I’m comfortable that you created your item_id for the search using the “Start with” test. Indexes will quickly allow you.

I don’t know MySQL, but in MSSQL, which has an index in the “Size” column, which has only a choice of S, M, L, most likely it will not achieve anything, the index will not be used, because it contains values ​​that are not selective enough, that is its faster to just go through all the data, and not "find the first record S in the index, now get the data page for this row ..."

The exception is that the query is covered by the index - that is, several parts of the WHERE clause are included in the index (and indeed, all of them, as well as SELECT columns). In this case, however, the first field in the index (in MSSQL) should be selective. Therefore, first put a column with a variety of values ​​in the index.

Having said that if your application has a selection list for size, color, etc., you should have these data attributes in separate columns in the record - and separate tables with lists of all available colors and sizes, and then you can check that Color / The size specified for the Product is actually defined in the Color / Size Tables. Resets garbage / garbage problem!

Your item_selected must be in a separate table so that it is "normalized". Do not store delimited list in one column, store it with separate rows in a separate table

So your USERS table will contain user_id and username

Your new, item_collected table will contain user_id and item_id (as well as possibly the purchase date or invoice number)

Then you can say “What Alex did” (your design has this meaning), as well as “Who bought Ss001” (which in your design will require plowing all the rows in your USERS table and separating the combination_ elements to find which ones contain Ss001 [1 ])

[1] Note that using LIKE is actually unsafe because you may have an item_id of "Ss001XXX" that matches WHERE items_collected LIKE '% Ss001%'