CODEWITHSUNDEEP
mysqlperformance
Tobias Baumeister
Tobias Baumeister

Reputation: 2147

Mysql performance sending data

I've got a table that looks like this:

ID (INT, 9, UNIQUE KEY) --- Item (INT, 3) --- Userid (INT, 9) --- Position (VARCHAR, 12)

Now I want to run the folling query:

SELECT COUNT( * ) 
FROM table
WHERE Position LIKE  'BAG%'
AND Userid = ".USERID."

The only possible Positions for those cases would be "BAG0", "BAG1", [...], "BAG6"
And those are always unique - that means that there is no Userid that has two Items in BAG2, for example.
But it's not always unique - for example two rows having "Userid: 82378, Positon: 'BOX1'" is possible

Even if I try the IN()-Clause doesn't change the result.

The "Sending data"-time for this query is always about 50ms.

Do you have any ideas how I could modify this table so this query works faster?

Upvotes: 1

Views: 90

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

This is your query:

SELECT COUNT( * ) 
FROM table
WHERE Position LIKE 'BAG%' AND
      Userid = ".USERID.";

This is a pretty simple query. The only way you can speed it up is by using an index. I would suggest a composite index of table(UserId, Position). MySQL should optimize the like because the initial prefix is constant. But, if you want to be really, really sure the index gets used, you can do:

SELECT COUNT( * ) 
FROM table
WHERE Position in ('BAG0', 'BAG1', 'BAG2', 'BAG3', 'BAG4', 'BAG5', 'BAG6') and
      Userid = ".USERID.";

Upvotes: 2

Related Questions