MySQL how does SELECT * actually work in MySQL/Query Efficiency

Question

I am building some AJAX queries that need to gather META data (column names) to query one input across several columns on the database...such as if you search for John Anderson and an email address of jand@email.com, "and" would match ANDerson and jAND@email.com

The query looks something like this:

// The joins might not make sense I took the database name out, since it is
// the same as the company I work for...
$querySyntax = "SELECT
                    idAccount, 
                    FirstName,
                    LastName,
                    Email,
                    Phone,
                    CCCity,
                    CCState
                FROM
                    account Right Join
                    states On account.CCState =
                    states.ID WHERE ";

$cols = $dbo->query("SELECT * FROM account");

$colcount = (count($cols) > 0 ? $cols->columnCount() : count($cols));

for ($ii=0; $ii < count($search); $ii++) {
    if ($ii>0)
        $querySyntax = $querySyntax . " AND ";
        $querySyntax = $querySyntax . "(";

    for ($i=0; $i<$colcount; $i++) {
        if ($i>0)
            $querySyntax = $querySyntax . " OR ";
            $meta = $cols->getColumnMeta($i);
            $colNames[$i] = $meta['name'];
            $querySyntax = $querySyntax . $colNames[$i] . " LIKE '%" . $search[$ii] . "%'";

        }
        $querySyntax = $querySyntax . ")";

    }

    $querySyntax .= " LIMIT 50";

$found = $dbo->query($querySyntax);
for($i=0; $row=$found->fetch();$i++) {
        $result[$i] = $row;
}

Ok, with that said, is there a better way to get the column names than the "SELECT * ..." query?

When you run SELECT * does it in fact read and store each record to memory? This could be extremely slow once the database is populated with the thousands of records we have.

If you're not using all fields, stick to naming them all, like you did. — jValdron, Jan 06 '12 at 17:47
If you're only after a list of the columns, could you not simply use 'limit 1' so you just get a single record, there's no need to return all the records if you only want meta data is there? — dougajmcdonald, Jan 06 '12 at 17:48
Don't worry about performance until you can _prove_ it's a problem. Until then, focus on other things that matter more _now_, like getting the product out the door. — cdeszaq, Jan 06 '12 at 17:49
What happens if you search for `O'malley`? [Use prepared statements](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php) or you're vulnerable to SQL injection. You're accepting input from the user and *just running it blindly*. — tadman, Jan 06 '12 at 17:52
@jValdron I have been thinking about changing it back as I have gotten some obscure results and results for "hidden" database things that don't need to match, — guyfromfl, Jan 06 '12 at 17:53
@tadman, thanks for pointing that out. I prepare almost every other query, unless its derived from internal data... — guyfromfl, Jan 06 '12 at 17:55
My point is that, if it isn't an issue _right now_, then don't worry about it. Especially when you are flying solo, leave problems until they are actually problems and don't get bogged down in details which may _never_ matter. — cdeszaq, Jan 06 '12 at 17:58

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

SHOW COLUMNS (and most other SHOW commands that query metadata) function internally as queries against the INFORMATION_SCHEMA. When you do that against InnoDB tables, MySQL attempts to refresh table statistics by querying random pages from the respective table. The result is that SHOW commands and queries against INFORMATION_SCHEMA are terribly slow.

There are a few workarounds:

SET GLOBAL innodb_stats_on_metadata=0 which disables the automatic recalc of table stats every time you view the metadata for InnoDB tables.

See Peter Zaitsev's recent blog: Solving INFORMATION_SCHEMA slowness, and the manual Dynamically Changing innodb_stats_on_metadata.
Use DESC account which is much faster than querying INFORMATION_SCHEMA.
Use PDOStatement::getColumnMeta() as you are doing, but use prepare() instead of query(). So it will parse your query but not execute it or attempt to fetch rows of results. Test this carefully because getColumnMeta() is "experimental" and probably buggy.

However, you will find that LIKE '%search%' predicates will perform poorly anyway. You should use a real fulltext search solution like Sphinx Search or Apache Solr.

PS: When coding a loop in PHP, it's better to calculate the count once before the loop, instead of on every iteration of the loop.

for ($ii=0; $ii < count($search); $ii++) {

Should be

$c = count($search);
for ($ii=0; $ii < $c; $ii++) {

Wow thanks for the tips! You really know your MySQL. You've posted on my posts before. Thanks for the loop tip as well. I never thought about it, but that cuts everything down to 1 test on the count() function... PS, I just ordered your book :D — guyfromfl, Jan 06 '12 at 19:19
You can also use the `FULLTEXT` index feature of MySQL if you make a secondary table for searching purposes that's of type MyISAM. — tadman, Jan 06 '12 at 22:12
@tadman, yeah, and thankfully fulltext for InnoDB is coming in MySQL 5.6. — Bill Karwin, Jan 06 '12 at 22:42

score 2 · Answer 2 · answered Jan 06 '12 at 17:55

This is pretty wasteful, and yes, this retrieves all records from that table. If you are interested in only listing the columns, you could probably use the SHOW COLUMNS FROM <your table> statement (docs) to retrieve just the list of columns. Moreover, you definitely should not do it before every query. Better to do it just once and then store the column list for future use, as they are really unlikely to change during the user session, aren't they?

MySQL how does SELECT * actually work in MySQL/Query Efficiency

2 Answers2