How to NOT select a column for MySQL table, does it make faster query

Question

I need to make a MySQL query where I don't want to select a column named description. Is there an easy way to specifically say to NOT select the column.

Example: I want to SELECT all but the description column. Can this be done or I have to write each one I need to pick instead?

id| Serverid | date_added | description ...
 1        yes   12.03.09     bad engine
 3        yes   12.04.09     ok engine
 2        no    12.05.09     ok engine
 4        yes   12.06.09     bad engine



$cars = lib::$db->GetAll("SELECT SQL_CALC_FOUND_ROWS
                c.*,
            FROM cars AS c
            ORDER c.date_added DESC
            LIMIT 10);

Does this answer your question? [How to select all the columns of a table except one column?](https://stackoverflow.com/questions/29095281/how-to-select-all-the-columns-of-a-table-except-one-column) — harunB10, Mar 24 '20 at 12:00
On a side note, what makes a **MUCH slower** query is SQL_CALC_FOUND_ROWS — Your Common Sense, Mar 24 '20 at 12:09
@YourCommonSense I need to get a rows number from which I then show pages of results with a load more button. — mk81, Mar 24 '20 at 17:14
*The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version.* - https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows — Dharman, Mar 24 '20 at 18:21

score 0 · Answer 1 · edited Mar 24 '20 at 18:17

0

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN1), '<Col To Omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TableName>' AND TABLE_SCHEMA = '<DBName>'), ' FROM <TableName>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

edited Mar 24 '20 at 18:17

Dharman

30,962
25
85
135

answered Mar 24 '20 at 12:20

Shantun Parmar

432
2
13

Do not plagiarize existing answers – Your Common Sense Mar 24 '20 at 12:26
I have recently used it so can's share if someone ask for help – Shantun Parmar Mar 24 '20 at 12:29

score -2 · Answer 2 · answered Mar 24 '20 at 12:04

-2

No, there is no sane way to do that except listing all fields manually, like

SELECT id, Serverid, date_added FROM table

On a side note, with any sensible setup, there would be no difference in performance if you do SELECT *

answered Mar 24 '20 at 12:04

Your Common Sense

156,878
40
214
345

1

If `description` contains a gigabyte of data per row, there will definitely be a difference in performance. – AKX Mar 24 '20 at 12:25
1

"with any *sensible* setup" – Your Common Sense Mar 24 '20 at 12:27
It's quite possible that the column contains a lot of data, which is why OP wants to elide it. – AKX Mar 24 '20 at 12:30
It makes a difference when using column types like text who's data is stored internally in an extra table requiring doing more scans! – Markus Zeller Jul 18 '22 at 11:12
@YourCommonSense It is extra work for the database and depending on the indexes, where conditions and table size it can make significant differences in performance. Using a Select * is more work than specifying single columns. – Markus Zeller Jul 18 '22 at 11:42

How to NOT select a column for MySQL table, does it make faster query

2 Answers2