1
vote
1answer
56 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
0
votes
0answers
45 views

What happens when an SSH session is terminated while a query is running?

I have a huge database on a dedicated server, whenever trying to modify the db I use PUTTY to establish an SSH connection and then do the work. Yesterday I started an INDEX query on an innodb table ...
0
votes
1answer
95 views

Using an enum index to limit records

A common issue, just not sure on the keywords. I have a table of assessments (14k+ records). Each assessment can be in many different states ('draft', 'proofing', 'amends', etc... and 'done'). Most ...
1
vote
0answers
30 views

table scheme definiation it this way that insert duplicate into another table

I have a table in which there is a column name with SP varchar(10) NOT NULL. I want that column always to be unique so i created unique index on that column . My table schema as follows : CREATE ...
0
votes
1answer
52 views

Optimizing a union query

SELECT id FROM user WHERE id IN ( 2,3,4,7,9,21,23,45,324,12,43,254 ) UNION SELECT DISTINCT user_id FROM workgroup_users WHERE workgroup_id IN ( 1,345,34,654,213,123,5,2,3,4 ) I know we should not ...
0
votes
1answer
93 views

Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

I have a table containing the list of visitors and this table has the following information. Visitor Browser Information Visitor Location Information Visitor Time Information No of Visits I have a ...
2
votes
1answer
205 views

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) ...
3
votes
1answer
730 views

Query not using index when it should

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table? ...
3
votes
1answer
319 views

Do I need to index a key that I partition by?

I have an InnoDB table of customers, partitioned by KEY, with each partition being one US state. I have an index on phone number, and the performance is great. We also need to SELECT by last name in ...