Best to Use * When Calling a Lot of Fields in MySQL

Best to use * when calling a lot of fields in mysql?

Note: naming all fields is of course a best practice, but in this post I will discuss only performance benefits, not design or maintenance ones.

---

The * syntax can be slower for the following reasons:

• Not all fields are indexed and the query uses full table scan. Probably not your case: it's hardly possible that all fields you return are indexed with a single index.

• Returning trailing fields from a table that contains variable length columns can result in a slight searching overhead: to return 20th field, previous 19 should be examined and offsets calculated.

• Just more data need to be returned (passed over the connection).

Since you need almost all fields, the last reason is probably the most important one. Say, the description TEXT field can be only 1 of 50 fields not used on the page, but can occupy 10 times as much space as all other fields together.

In this case it will be of course better to name all fields and omit the long fields you don't need.

What column data type should I use for storing large amounts of text or html

Yes, it will be better if you can store the values in the "TEXT" data type. For more details, please read this article.

Regarding knowledge of storage requirements, you can read this one.

Hope it helps.

MySQL: For ORDER BY calls (different fields), do I create an index for each field?

There's no silver bullet here, you will have to test this for your data and your queries.

If all your queries (e.g. WHERE id=10) returns few rows, it might not be worth indexing the order by columns. On the other hand, if they return a lot of rows, it might help greatly.

If you always order by atleast one of the fields, consider creating an index on that column, or a compound index on some of these columns - keep in mind that if you have a compound index on (num_field_1,num_field_2) and you order only by num_field_2 , that index will not be used. You have to include the leftmost fields of the index to make use of it.

On the other hand, you seem to have a lot of different fields you order by, the drawback of creating an index on each an every one of them is your indexes will be much larger, and your inserts/deletes/updates might start to get slower.

Basically - there is no shortcut. You'll have to play around with which indexes works best for your queries and tune accordingly, while carefully analyzing your queries with EXPLAIN

MySQL specify every column in query rather than using SELECT *

Select * is inherently less efficient because the database has to look up the columns. Further if you have even one join you are sending unnecessary repeated data which is wasteful of database and network resourses, particularly if you do it on every query. Finally, select * doesn't specify the order of the columns, so if someone foolish drops and recreates the table with the columns in a different order, you may suddenly have your Social security number showing up in your first name column on the form or report. And if someone adds a column that you don't want displayed everywhere (say for auditing purposes or notes about the customer that you don't want the customer to ever see) you are in trouble. Further, if you do add columns, you need to determine where they should show up anyway and why not just have them willy nilly show up everywhere. Select * is an extremely bad SQL antipattern.

How to call MySQL function for every column that is present in table?

But i have no clue how to do it for all columns at once.

You just can't - there is no such shortcut in the update syntax.

You can do this with a single update statement, but you need to enumerate each and every column, like:

update table_name set
    column_name1 = function_name(column_name1),
    column_name2 = function_name(column_name2),
    column_name3 = function_name(column_name3)

An alternative would be to use dynamic SQL to programatically generate the proper query string from catalog table information_schema.columns, and then execute it. This seems uterly complicated for what looks like a one-shot task... But here is sample code for that:

-- input variables
set @table_schema  = 'myschema';
set @table_name    = 'mytable';
set @function_name = 'myfunction';

-- in case "GROUP_CONCAT()" returns more than 1024 characters
set session group_concat_max_len = 100000;

-- build the "set" clause of the query string
select
    @sql := group_concat(
        '`', column_name, '` = ', @table_schema, '.', @function_name, '(`', column_name, '`)'
        separator ', '
    )
from information_schema.columns
where table_schema = @table_schema and table_name = @table_name;

-- entire query string
set @sql := concat('update ', @table_schema, '.', @table_name, ' set ', @sql);

-- debug
select @sql mysql;

-- execute for real
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

MySQL Like multiple values

The (a,b,c) list only works with in. For like, you have to use or:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

How to query MySQL by one of the field's subvalue?

Storing multiple indexes or any references in one field is strictly not advised.
You have to create something that I call "rendezvous" table.

In your case it has:

 - ID
 - UserID (foreign key)
 - InterestID (foreign key)

Every single person can have multiple interests, so when a person adds a new interest to himself, you just add a new row into this table, that will have a reference to the person and the desired interest with a foreign key NOT NULL.

On large-scale projects when there are too many variations available, it is advised, to not to give an ID row to this table, but rather set the two foreign keys also primary keys, so the duplication will be impossible and the table-index will be smaller, as well as in case of lookup, it will consume less from the expensive computing power.

So the best solution is this:

 - UserID (foreign key AND primary key)
 - InterestID (foreign key AND primary key)

Best way to do a weighted search over multiple fields in mysql?

Probably this approach of doing a weighted search / results is suitable for you:

SELECT *,
    IF(
            `name` LIKE "searchterm%",  20, 
         IF(`name` LIKE "%searchterm%", 10, 0)
      )
      + IF(`description` LIKE "%searchterm%", 5,  0)
      + IF(`url`         LIKE "%searchterm%", 1,  0)
    AS `weight`
FROM `myTable`
WHERE (
    `name` LIKE "%searchterm%" 
    OR `description` LIKE "%searchterm%"
    OR `url`         LIKE "%searchterm%"
)
ORDER BY `weight` DESC
LIMIT 20

It uses a select subquery to provide the weight for ordering the results. In this case three fields searched over, you can specify a weight per field. It's probably less expensive than unions and probably one of the faster ways in plain MySQL only.

If you've got more data and need results faster, you can consider using something like Sphinx or Lucene.

How to select a column name with a space in MySQL

Generally the first step is to not do that in the first place, but if this is already done, then you need to resort to properly quoting your column names:

SELECT `Business Name` FROM annoying_table

Usually these sorts of things are created by people who have used something like Microsoft Access and always use a GUI to do their thing.

---

Related Topics