Migrating from an old version of CFMX to Lucee 4.5. We are storing client
variables in a MySQL database. When Lucee automatically created the
database table (cf_client_data), it did not create any indexes on the table.
For performance reasons, should I manually create an index on the table?
If so, what fields should be indexed?
The schema definition for the table is:
expires varchar(64)
cfid varchar(64)
name varchar(255)
data longtext
Did you ever get an answer to this?
Sure you’'ll need/should set indexes. Did you see the “tip” in the Lucee Documentation already?
You still could log your MySQL queries and take a detailed look at the SQL statements Lucee is doing behind the scenes and then you’ll see what fields are relevant for selection.
update: I’ve just seen that the tip in the link I’ve mentioned above tells about session storage, but doesn’t tell about cf_client_data. 
I had a little time, so I’ve just created a quick sample page and added everything to Lucee 5.3.6.61 administrator, and here is the reference to complete this old post. Maybe there wasn’t any other posts, because Lucee creates the table cf_client_data “on the fly” as soon as you set a variable of scope “client” in your app for the first time. I’ve made this with MariaDB, but it still is compatible with MySQL
and here is a table creation export for a default MariaDB 10.4.10 InnoDB Engine. This is just as a reference, so you can see al the important data fields and indexes that Lucee has created.
CREATE TABLE IF NOT EXISTS `cf_client_data` (
`expires` varchar(64) NOT NULL,
`cfid` varchar(64) NOT NULL,
`name` varchar(255) NOT NULL,
`data` longtext NOT NULL,
UNIQUE KEY `ix_cf_client_data` (`cfid`,`name`,`expires`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
I’m going to add this as a contrib to Lucees documentation as soon as I can, because this is something I’ve found that is still missing in the FAQ section (@Zackster)
Hope it helps.
Many thanks! 
So - We hit a fairly severe performance issue in PRODUCTION today related to this thread. Our session database seized up pretty bad, and the process list showed a ton of session read / write requests stuck behind this statement:
CREATE UNIQUE INDEX ix_cf_client_data ON cf_client_data(cfid, name, expires)
Sooo. I thought I had applied this back a month ago. But it turns out I only applied it to ONE of the session databases (we have two - thinking it would help performance).
I even think I saw it in the process list again after it finished. It caused a massive queue to form on the database, and the Lucee servers crashed. We failed back to 5.2.8.50 until this is sorted.
My question - Does Lucee try to auto-build this index somehow even after the tables are built?
I posted a new topic on this here:
https://lucee.daemonite.io/t/cf-client-data-not-deleting-in-5-3-6-61/7293