Category Archives: Databases

400 Million Records in MySQL

So I’m trying to figure out a way to make searching a VARCHAR in MySQL “fast” when there are 400 million rows.
I tried the UNIQUE approach using alter table names add unique(name(15)); in the table but I have some duplicates apparently, so now I’m trying a different method.

I’m going to create multiple tables;  a-z, 0-9.

Based on the input query such as SELECT * FROM name WHERE name='rich' I’ll split that out and re-write the query such as SELECT * FROM r_name WHERE name='rich'.  r_name table is considerably smaller than the entire name table of 400 million rows.  I’ll just say 30 million.

Here’s my bash script to process the main plain text file that I’ll then create LOAD for in MySQL for each split file.

#!/bin/bash

# Split names.txt into a.txt, b.txt, c.txt etc.

for x in {a..z}
do
 echo Scanning $x
 grep -i ^$x names.txt >$x.txt
done
for x in {0..9}
do
 echo Scanning $x
 grep -i ^$x names.txt >$x.txt
done

echo.
echo Done

This is currently processing, so I’ll update when this is finished and see how much more optimized this will be.

Update

Well, crap.  I think I screwed up when I initially parsed out the main names.txt and tried to only grab unique lines.  I should have passed a case insensitive uniq command using uniq -i.

So now I’ll try doing that again and reloading the database.

Update 2

I reloaded the names database from text file.  Doing a create index idx_name on names(name(767)); changed a query execution time of select * from names where name='rich' from 12 minutes to 0.03 seconds.  I’m happy now. And really happy I don’t need to create a bunch of tables and add logic to some PHP/MySQL. Just goes to show I’m green behind the ears when it comes to database technology.

Note: Not quite 400 million after removing duplicates and invalid names from the file 😉

MariaDB [data001]> select max(id) from names;
+-----------+
| max(id)   |
+-----------+
| 321995408 |
+-----------+
1 row in set (0.00 sec)
MariaDB [data001]> select * from names where name='rich';
+----------+-------------+
| id       | name        |
+----------+-------------+
| 86382207 | rich        |
+----------+-------------+
1 row in set (0.00 sec)

SQL Server 2016 SP1 Edition Limit Changes

Microsoft made the following changes in their documentation (see screenshot above) to accurately reflect the memory limits on lower editions of SQL Server.

Key Points

  • The limits for In-Memory OLTP data is per database.
  • The limits for Columnstore segment cache is per SQL Server instance across all the databases in the instance.

Example Scenario

A Standard Edition of SQL Server has buffer pool memory limited to 128GB, so the data and index pages cached in buffer pool is limited by 128GB. Starting with SQL Server 2016 SP1, you can have an additional 32GB of memory for Columnstore segment cache per instance and an additional 32GB of memory quota for In-Memory OLTP per database. In addition, there can be memory consumed by other memory consumers in SQL Server which will be limited by “max server memory” or total memory on the server if max server memory is uncapped.

Change WordPress Password using MySQL

If the “Forgot” password option doesn’t work for you (which it really should), then you can change the password of your self-hosted WordPress login.

Check the contents of wp-config.php for the database login. Use that login information for MySQL login.

define('DB_NAME', 'wpdatabase');
define('DB_USER', 'wpdbuser');
define('DB_PASSWORD', 'wpdbpassword');

Replacing DB_USER and DB_NAME from that command with the values from wp-config.php, enter the following command to connect to the database on your server.

mysql -uDB_USER -p DB_NAME

Get a list of users in the WordPress database.

select user_login from wp_users;

This might return multiple entries, but it was just one in my case: admin

To change the password, issue the following. Be sure to change ‘admin’ to the value of your user_login.

update wp_users set user_pass=md5('NEW_PASSWORD_HERE') where user_login='admin';

All done. Now you can log into your WordPress administration panel with the new password you specified.

MSSQL Find Last Date & Time Database Table Accessed

This is a T-SQL that will show all the last access date and time for Select, tables in a database.

select
t.name
,user_seeks
,user_scans
,user_lookups
,user_updates
,last_user_seek
,last_user_scan
,last_user_lookup
,last_user_update
from
sys.dm_db_index_usage_stats i JOIN
sys.tables t ON (t.object_id = i.object_id)
where
database_id = db_id()

Source: SQL Authority

Additional Information/Links:  SQL Blog