SQL and MySQL

Reset the auto increment value

ALTER TABLE tablename AUTO_INCREMENT = X

Querying from the command line

mysql -u root -p root -D test -e 'show tables from test;'

show columns from table_name;
show tables;
show full processlist # load

Query to get inspiration from

-- ON DUPLICATE
INSERT INTO table (id, time) VALUES (1,1),(2,2),...,(10,10) ON DUPLICATE KEY UPDATE time=VALUES (time)

-- ORDER BY FIELD
SELECT * FROM medias_contributions WHERE marker IN ('media:a','media:b','media:c') ORDER BY FIELD (marker, 'media:c', 'media:a', 'media:b')

-- LAST_INSERT_ID
UPDATE table SET num=num+10, id=LAST_INSERT_ID(...)

-- REGEX
SELECT * FROM animal WHERE nom REGEXP "^.{5}$";

-- NATURAL SORTING TRICK
SELECT names FROM your_table ORDER BY names + 0, name

-- COUNT ROW (this is not more efficient than two queries)
SELECT SQL_CALC_FOUND_ROWS t1.* (count les row malgre le limit) puis SELECT FOUND_ROWS()

-- DATES
UPDATE events SET date_starts = DATE_ADD(date_starts, INTERVAL 14 DAY)
update contributions set updated = adddate(updated,interval 3 day),  created = adddate(created,interval 3 day), published = adddate(published,interval 3 day) where id > 40000

-- GROUP CONCAT
SET [GLOBAL | SESSION] group_concat_max_len = val;
select group_concat(images.id order by images.id, ','), contributions.id
from images join contributions on images.object_id = contributions.id
where (object_type = 1 or object_type is null) and place = 1 group by contributions.id having count(*) > 1

Geolocation

SET @latitude := 48.890980579936;
SET @longitude := 2.4131774995498;
SELECT city_name,
(6366*acos(cos(radians(@latitude))*cos(radians(`city_latitude_deg`))*cos(radians(`city_longitude_deg`) -radians(@longitude))+sin(radians(@latitude))*sin(radians(`city_latitude_deg`)))) AS dist
FROM cities_france
HAVING dist <= 10
ORDER by dist ASC

Variables in SQL

$sql = "SET @rank := 0;";
$sql = '/* update_skill update */ UPDATE skill SET skill = 100-((100*(bayesian_time_taken-'.$data['min'].'))/('.$data['max'].'-'.$data['min'].')), rank = greatest(0,@rank := @rank + 1) ORDER BY bayesian_time_taken ASC';

Count and date

SELECT 'Week 1', COUNT(*) FROM Ads WHERE FROM_UNIXTIME(createdAt) BETWEEN DATE_ADD('2014-12-29 00:00', INTERVAL 0*7 DAY) AND DATE_ADD('2014-12-29 00:00', INTERVAL 1*7 DAY) -- UNION ... etc

SELECT
   extract(week from FROM_UNIXTIME(createdAt)),
   extract(year from FROM_UNIXTIME(createdAt)),
count(*) FROM Ads GROUP BY
   extract(week from FROM_UNIXTIME(createdAt)),
   extract(year from FROM_UNIXTIME(createdAt))
;

Saving

mysqldump --user=save --password=save --all-databases | bzip2 > /root/save/databases_$(date +%Y%m%d%H%M).sql.bz2`

Performance check

EXPLAIN SELECT SQL_NO_CACHE

Int type and size