1 / 12

Comparing FlatSQL, MySQL Queries, and Unix Tools for Effective Database Querying

This paper presents a detailed analysis of querying methods: FlatSQL, MySQL, and Unix tools like grep. The authors Konrad Lorincz, Kevin Redwine, and Jesse Tov from Harvard University explore criteria for choosing the best option, including ease of use, performance, and language familiarity. They define FlatSQL, highlighting its SQL-like syntax for flat files, and compare its performance against MySQL and grep through various experiments. The results emphasize the trade-offs between simplicity, speed, and data management capabilities in different querying contexts.

siran
Télécharger la présentation

Comparing FlatSQL, MySQL Queries, and Unix Tools for Effective Database Querying

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. FlatSQL vs. MySQLQueries using Unix tools vs. a DBMS Konrad Lorincz Kevin Redwine Jesse Tov Harvard University September 14, 2014

  2. Dilemma:How to Perform Queries? Options • Use a simple search tool (grep) • Use FlatSQL • Use MySQL Research Question • How do you decide which option to choose?

  3. Criteria • Ease of Use • Text files – portable, easy to edit, existing Unix tools • Language – learn new language, use standard language • Performance • Timing

  4. What is FlatSQL • FlatSQL is like SQL • Queries have the form "SELECT ___ FROM ____ WHERE ___“ • There is added syntax for dealing with flat files • A lot of complexity has been removed • Sample Query: /etc/mtab $ flatsql SELECT m.1, m.2 \ FROM /etc/mtab AS m \ WHERE m.3 = \"ext3\"

  5. Sample Query: /etc/group and /etc/passwd SELECT user FROM /etc/passwd WITH /:/ AS p (user x uid gid gecos home shell), /etc/group WITH /:/ AS g (group x gid memb) WHERE p.gid = g.gid AND group = "cs265" Output konrad redwine tov

  6. Query Translation SELECT user FROM /etc/passwd WITH /:/ AS p (user x uid gid gecos home shell), /etc/group WITH /:/ AS g (group x gid memb) WHERE p.gid = g.gid AND group = "cs265" awk "{system(\"awk \\\"\\\\\\\"\" \$4 \"\\\\\\\==\\\\\$3 && \\\\\$1==\\\\\\\"cs265\\\\\\\" {print \\\\\\\"\" \$1 \"\\\\\\\"}BEGIN{FS=\\\\\\\":\\\\\\\"}\\\" /etc/group\")}BEGIN{FS=\":\"}" /etc/passwd

  7. Experiments • Selects (One Relation) • Grep vs. FlatSQL vs. MySQL • indexed vs. non-indexed • Joins (Multiple Relations) • FlatSQL vs. MySQL • indexed vs. non-indexed • Start with cold cache each time

  8. Select – 1 RelationIndexed Data

  9. Select – 1 RelationNon-Indexed Data

  10. Join – 2 RelationIndexed Data

  11. Join – 2 RelationsNon-Indexed Data

  12. Conclusion • Simple search tool (grep) • 1 Relation and just regular expression matching • Non Indexed – could be very large • FlatSQL • Comparison Operators • Small joins – performance not critical • Prefer to keep data in flat files • MySQL • Performance is Critical • Data has to be indexed • Multiple Joins

More Related