I was hearing horror stories about people whose BlueHost accounts were suspended for 'CPU Overuse'. I contacted BH (because I was considering moving there) about this and they said the biggest killer of CPU was "SELECT * FROM". As somewhat of a noob to PHP, I don't know of any other way to get a whole row of data other than this:

"SELECT * FROM table WHERE colname = variable LIMIT 1"

Is there something I'm missing on optimizing my code?

Your advice is appreciated.

    MySQL 101: If you use "select * from" you are automatically using 2 queries. One for it to go find the names of all the columns and another to actually run the query in the form "select col1, col2, col3,... coln". Even if it involves you writing out every single column name, do it. It may take you a while to do, but you only need to do it once. Your MySQL server may need to do it many many times.

      Do you truly need every single column in that row?

      EDIT:

      piersk wrote:

      MySQL 101:

      Wow... I must have skipped the basics when I learned MySQL... I never knew that! :o

        While on the subject of i tend to use "SELECT count() FROM ... " when i use the count function, will it use more resources than "SELECT count(row_name) FROM ..." ?
        Nice to know if i should change my coding habbit ? 😉

          Actually, if you're using a MyISAM table, I believe COUNT(*) has some optimization advantages as opposed to just COUNT(id) or some other abritrary field.

            bradgrafelman wrote:

            Do you truly need every single column in that row?

            Uh... yeah. That's why the data was put there to begin with: to retrieve it later.

            The question is, how would I optimize/simplify the request?

            This DB is employment applications which must have almost every field displayed to the office worker.

              mrhinman wrote:

              Uh... yeah. That's why the data was put there to begin with: to retrieve it later.

              Right, but do you need all of the data for this SELECT query?

              Either way, the solution is to name only the columns you're using, e.g.

              SELECT col1, col2, col3 FROM myTable

              Other than that, there may be other optimizations you could do to improve the queries (e.g. using JOINS instead of multiple queries, indexing columns that are frequently used in WHERE clauses, etc.).

                piersk wrote:

                MySQL 101: If you use "select * from" you are automatically using 2 queries. One for it to go find the names of all the columns and another to actually run the query in the form "select col1, col2, col3,... coln". Even if it involves you writing out every single column name, do it. It may take you a while to do, but you only need to do it once. Your MySQL server may need to do it many many times.

                EH 😕 Sorry Piersk, but that is just rubbish. Regardless of whether you select * or by column name, the db engine is going to have to first read the table definition to determine the order, data type and hence size of each column to determine its offset within a row. If you have variable length columns then these will have to be read individually to calculate the offset for the next column. You will only see real gains in efficiency if you are selecting just a few columns from the front of the record. If you want all columns then naming them will save nothing.

                Mr Hinman, the main reason your host sez that is because they are used to dealing with amateurs who have not normalised their data properly so they have needlessly wide tables and who use select * when they only need one or two columns. Such users are also likely to write queries within loops instead of learning how to use joins and where clauses.

                Dunno about BlueHosts but there are loads of great hosting deals out there with companies who don't overload their servers and then blame the users. Shop around if you are worried.

                  Thanks all for your fine comments. I do use WHERE and LIMIT as appropriate. LIMIT 1 is my most common, since each DB item is unique in most tables. For employment histories (in another table) there can be many entries with similar identifiers, but I set LIMIT 25 since it is unlikely anyone has 25 prior employment histories.

                  In some cases, where a search is used, SELECT * FROM with no limits is used but WHERE clauses are used.

                  And, Roger, technically I am an amateur when it comes to PHP. I've got about 16 months experience writing in PHP and I still don't know everything I should. Anywhere I can improve is great for me and my customers/hosts.

                  Brad: Most queries require ALL of the columns in a particular row, but not all the rows. These are employment applications and no data can be left out when the data is retrieved, so nearly every single column is used. I've got one table with over 300 columns that stores skill data for each applicant. LIMIT 1 still applies here, which theoretically should stop the search process once the unique row is located.

                  Thanks again, folks, for your insightful remarks!

                    300 columns for skill data ??? Whoops, sounds like you got the design wrong there. You see, I doubt that any applicants have 300 skills, so most of those columns are gonna be NULL or FALSE or whatever, now aren't they.

                    Generally one would have a skills table, an applicants table and a skills/applicants matrix table to link the 2.

                    LIMIT 1 when searching for applicants with a required skillset is also not very clever. What happens when several applicants match? Don't overuse LIMIT.

                      Though I doubt there's any such instances in your application, if you need to randomly retrieve a row, there's an alternative method to the "ORDER BY RAND()" approach posted in this forum.

                      While this may be a fun exercise at maximizing the optimization of your application, I second the notion of combating a limiting host by searching for another if it comes to that.

                        Write a Reply...