0

So, the context is: I have a site in which many pages may need the information about one table, say for instance, 'films'. This table has many fields, like title, language, year, description, director... And perhaps in one page I need only the title and the id of some rows and in another I also need the description.

So the question is: should I code a database manager (I am using MySQL) that retrieves all the fields of the rows that satisfy a condition (I guess the WHERE clause should be passed as a parameter)? Or should I be able to specify which fields are needed? I thinks this cannot be done easily with mysqli (because prepared statements require to specify beforehand the number of fetched fields), so for this to work I would need to use PDO instead, which I haven't used yet. Is it worth it this last approach? Or there is not really a big difference in performance if I retrieve the whole information about those rows?

Thank you in advance.

Ryckes
  • 34
  • 6
  • are you using ONE row most of the time or a couple of rows or eventually the whole table most of the time in your application? – herrjeh42 Dec 07 '13 at 14:39
  • It's definitely more than a couple of rows but usually not the whole table. Though there are a couple of pages where the whole is needed, when listing for instance, but I think that this special case could be coded in a single method of the manager. – Ryckes Dec 07 '13 at 14:50
  • Does your 'films' table have any CLOB/TEXT columns? Showing your table would be helpful. If not, the difference between fetching some fields and all the fields isn't going to make a performance difference. – Thomas Jones-Low Dec 07 '13 at 14:53
  • In the example yes, the table I was thinking of has two TEXT columns. Nonetheless I was thinking in coding a similar manager for other tables. But I did not know if it would be better to code every used case as a method (like getTitleAndId($where), getAll($where), getField1Field2Field3($where)... I thought this dirty), a method to retrieve all (get($where) which would fetch all fields) or a method like get($fields, $where), being $fields an array of field names. – Ryckes Dec 07 '13 at 14:58
  • How many rows of data are in your table? If its more than 10, adding a where clause to the query is essential. I would start with the `get($fields, $where)`. But be aware of the inputs into the query don't cause security problems: http://xkcd.com/327/ – Thomas Jones-Low Dec 07 '13 at 15:03

1 Answers1

1

Based upon the comments above, My answer to your question(s) is

  1. Retrieving some fields vs all fields isn't a real performance consideration until you are dealing with one or more CLOB/TEXT columns which have a lot of text in them. Good database practice indicates you should always specify which fields are returned from a query.

  2. Any query against any table should have a where clause to restrict the number of rows returned. Especially if you are looking to query exactly one row.

  3. Your question implies you are writing a wrapper layer around the queries to hide this complexity. Don't do this. Get an existing PHP library that does this work for you. See for example: Good PHP ORM Library? . There are a number of subtle issues, like security, which you will overlook.

Community
  • 1
  • 1
Thomas Jones-Low
  • 7,001
  • 2
  • 32
  • 36
  • 3. is exactly what I am looking for. I will check Doctrine and RedBean and see if any of them fit my needs. Thank you! – Ryckes Dec 09 '13 at 19:02