PHP Prepared Statements and MySQL Table Design

Ed Barnard

Saturday 23 September 2017 from 14:40 to 15:40

Talk in English - US at Madison PHP Conference 2017
View Slides: https://www.dropbox.com/s/to1ldcmaxtkjjgr/Prepared-V05.pdf?dl=0
Checkout the code: https://github.com/ewbarnard/PreparedStatements
Short URL: https://joind.in/talk/21043 (QR-Code (opens in new window))

Avg. Rating

When using a PHP framework, standard practice is to use an Object-Relational Model (ORM) for database access. However, with high-volume logging and statistics-gathering, it pays to go "old school" with PHP prepared statements. Meanwhile, when MySQL tables quickly grow by millions of rows, table storage space becomes an issue. Our table design must focus on keeping these tables more compact and efficient. Here too, prepared statements simplify both coding and table design. We'll be using CakePHP 3's excellent support for PHP prepared statements, but all concepts are native to PHP and apply to any project striking this use case.

Comments

Comments are closed.

Jim Dreger at 15:32 on 23 Sep 2017

Great talk on prepared statements.
Wish we had more time.

Michael Redig at 15:36 on 23 Sep 2017

Very informative and enjoyable presentation.

I feel like the slides were a bit confusing separating the pros and cons of the prepared statements versus hand coding, but were clarified with asking questions. Maybe separate slides or pro/con columns visually separating them? (same for the use cases)

Michael Redig at 15:38 on 23 Sep 2017

I forgot to mention that the slides provided on dropbox varied slightly from what was shown on screen.

Liam Wiltshire at 17:06 on 24 Sep 2017

A very good talk that I found useful and instructive. It's a shame that we ran out of time so some slides needed to be skipped , but the content that we did see was very interesting and provided a new way to look at insert performance. Well worth checking this talk out - thanks Ed!

Alex Fraundorf at 08:04 on 25 Sep 2017

A great talk on trade offs between performance and security when using prepared statements, and more importantly why we always need to use prepared statements with user supplied data.
As with all of Ed's talks, it was not only informative but entertaining.
Thanks Ed!

Ed Barnard (Speaker) at 10:12 on 25 Sep 2017

Thanks everyone for the useful suggestions. I considered dropping the benchmark code walk-through to save time, but feedback shows it was useful. Instead I'll focus on getting the questions we asked/answered during the talk into the presentation. That should clarify the focus and help me not run out of time!