A new star is born: sqlexamples.org goes online!
We're proud to announce sqlexamples.org, the community for SQL and NoSQL examples! Our aim is it to build a library that provides free (as in free speech) database examples for all practical purposes, either related to SQL or NoSQL. To accomplish this goal, we need your help!
Example: MySQL index usage: The LIKE operator, wildcard patterns and FULLTEXT indices
If the LIKE operator is used for pattern-matching and the pattern starts with a wildcard (%), MySQL doesn't make (efficient) use of indices. A scan of the entire index has to be performed. This usually leads to a bad performance, a high(er) system load and can slow down the MySQL server completely. Under certain circumstances, a FULLTEXT index can help to solve such performance problems. In this example we use a FULLTEXT index for searching employees with the same surname. Their name is stored in one field (employee).
Example: Subquery syntax example: EXISTS / NOT EXISTS
You can use "EXISTS" in combination with a subquery to check whether the subquery returns any rows. If the subquery returns at least one row, EXISTS returns true. To negate this behavior, simply use "NOT EXISTS" instead. If the subquery doesn't return any rows, "NOT EXISTS" returns true. It's important to note that no comparison is made between the data in the outer query which uses "EXISTS"/"NOT EXISTS" and the rows returned by the subquery. Take a look at the following example, where we try to check the database of an eCommerce shop for consistency using "NOT EXISTS":