Programming stuff: Bash, Linux, SQL and Java

below is a case with only 2 varchar columns and an int
create table example(
id int unsigned auto_increment not null,
column1 varchar(200),
column2 varchar(200),
column3 int unsigned,
primary key(id)
);

mysql -u USERNAME -pPASSWORD DBNAME -e "insert into example (column1,column2,column3) values (char(FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20))),char(FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20)),FLOOR(65 + (RAND() * 20))),FLOOR(1000 + (RAND() * 1000)))"

it will generate uppercase strings and integers between 1000 and 2000 ,and insert them into the table.
it is important to put unsigned, thus you will increase the range for the primary key. you don’t need any negative values for id anyway.

….not anymore, if you use IF.

there are cases where you can get rid of OR and get better performance in mysql.
here is a case a table:
book
id
french_name
engl_name

the books have only one name and only one, the other being null
you have a form on your web page and want to do a search for the books that contain a certain substring of characters. the client wants to retrieve both english and french name that contain that substring.

first shot: select * from book where french_name like ‘%CHARS%’ or engl_name like ‘%CHARS%’

this is very expensive.
the better solution:
select * from book where if(french_name is null,engl_name,french_name) like ‘%CHARS%’

on my system is 2 times faster.