Jump to content

Archived

This topic is now archived and is closed to further replies.

fenway

"The" MySQL Sticky

Recommended Posts

http://www.sqlzoo.net/

 

An oldie but a goodie... site is always a little too slow, but the fact that they make you write queries you can test is a definite plus for anyone who isn't versed in SQL statement syntax.

Share this post


Link to post
Share on other sites

A fantastic reference card-style sheet can be found here; available as a PDF or a PNG.

 

Visibone now has one too!

Share this post


Link to post
Share on other sites

So, for all of us who are used to "ORDER BY RAND() LIMIT 1" -- which doesn't scale well at all -- here's a "better" way.

 

With a JOIN (concept from Jan Kneschke): 

SELECT
*
FROM
tablename AS t1
INNER JOIN 
(
    SELECT
    ROUND(RAND() * (SELECT MAX(id) FROM tablename)) AS id
) AS t2 
ON
t1.id >= t2.id 
ORDER BY
t1.id ASC 
LIMIT 1;

 

Or with subqueries:

SELECT * FROM tablename
WHERE id >= FLOOR( RAND( ) * ( SELECT MAX( id ) FROM tablename ) )
ORDER BY id ASC
LIMIT 1

 

In principle, this can be extended to N rows... just remember that it's possible than <N rows are retrieved, depending on the distribution of id values -- so if it really matters that you get N back, you may want to ask for some more, just in case.  Either way, you'll still have to check the size of the result set.

 

Hope that helps.

Share this post


Link to post
Share on other sites

This page contains some in-depth, extremely well-written articles on query optimization in very common situations.

 

A collection of fantastic resources on the subject of MySQL Performance:

Share this post


Link to post
Share on other sites

MySQL's tech resource article on database normal forms is an excellent read.

 

NEW!!! - A large (160MB, 4M record) sample database with test suite for MySQL -- fantastic for running "real" queries.

 

In addition, MySQL provides a number of sample databases for testing purposes; personally, I find this one to be the most useful for playing around with SQL statements and such.

 

The Zip Code Database Project exists to provide US Zip Codes in their entirety; latitude and longitude coordinates included! The downloads are in CSV and MySQL table dump formats.

 

Also, there's an pretty good online "data generator", and SQL is one of the options... although personally, I prefer the integers table approach (courtesy of Baron Schwartz):

 

set @num_gamers    := 10000,
    @num_countries := 5,
    @num_games     := 10;

drop table if exists gamer;
drop table if exists game;
drop table if exists country;
drop table if exists score;
drop table if exists semaphore;

create table gamer(
   gamer int not null,
   country int not null,
   name varchar(20) not null,
   primary key(gamer)
);

create table game(
   game int not null,
   name varchar(20) not null,
   primary key(game)
);

create table score(
   gamer int not null,
   game int not null, 
   score int not null, 
   primary key(gamer, game),
   index(game, score),
   index(score)
);

create table country(
   country int not null,
   name varchar(20) not null,
   primary key(country)
);

-- I use the integers table to generate large result sets.
drop table if exists integers;
create table integers(i int not null primary key);
insert into integers(i) values(0),(1),(2),(3),(4),(5),(6),(7),(,(9);

insert into country(country, name)
   select t.i * 10 + u.i, concat('country', t.i * 10 + u.i)
   from integers as u
      cross join integers as t
   where t.i * 10 + u.i < @num_countries;

insert into game(game, name)
   select t.i * 10 + u.i, concat('game', t.i * 10 + u.i)
   from integers as u
      cross join integers as t
   where t.i * 10 + u.i < @num_games;

insert into gamer(gamer, name, country)
   select th.i * 1000 + h.i * 100 + t.i * 10 + u.i,
      concat('gamer', th.i * 1000 + h.i * 100 + t.i * 10 + u.i),
      floor(rand() * @num_countries)
   from integers as u
      cross join integers as t
      cross join integers as h
      cross join integers as th;

insert into score(gamer, game, score)
   select gamer.gamer, game.game, floor(rand() * @num_gamers * 10)
   from gamer
      cross join game;

Share this post


Link to post
Share on other sites

Basically, this blog contains a regularly-updated run-down of MySQL functions, with a short description and a few hints on how to use them; RSS feed here.

Share this post


Link to post
Share on other sites
This resource covers a very broad range of topics... it's worth a look, though, especially if you're stumbling in the early stages of configuration.

Share this post


Link to post
Share on other sites

Hopefully this presentation will stay online at scribd...  it's simply fantastic, probably the best I've come across in recent memory.  At 220 slides, it's quite lengthy -- but the lessons learned are invaluable, so be sure to read all the way to the end.

 

A MUST READ!!!!

 

EDIT: This year's version of the presentation -- some really great stuff in here, particuarly about hierarchies.

Share this post


Link to post
Share on other sites

A four part tutorial that explains the PHP / MySQL extensions - mysql, mysqli, and pdo_mysql - with simple examples is now accessible from Sun Developer Network.

 

Application developers who are new to the development of MySQL database applications with PHP are the target audience of this tutorial.

 

Covers:

  • Part 1: Using the MySQL Improved Extension, mysqli
  • Part 2: Using the MySQL Extension, mysql
  • Part 3: Using the PDO Extension With MySQL Driver, pdo_mysql
  • Part 4: Using the MySQL Native Driver for PHP, mysqlnd

 

Tutorial is here.

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.