CODEWITHSUNDEEP
phpsqlindexing
112
112

Reputation: 13

SQL query is slow: indexes?

I have the following SQL query, but the query is very slow: (page loading 2 seconds).

mysql_query("select * from table_test 
WHERE ((owner = '$id' AND ownersecond = '0') OR ownersecond = '$id') 
AND catogory = '1' AND type = '$car[id]' AND caseB = '0' 
AND (timeback < TIMESTAMP(NOW()) OR timeback = '0000-00-00 00:00:00') ");

I want to improve the SQL statement by using indexes. I already have an index on the row: owner and owner second. Is it useful to put indexes on type, caseB and tieback? Or do i slow the sql more and more? Because of to many indexes?

Or: Is it better to rewrite above SQL into a faster query? And does anyone have a suggestion for a better written query. Thanks in advance.

Upvotes: 1

Views: 58

Answers (2)

user399666
user399666

Reputation: 19879

Use EXPLAIN to a diagnose what index is being used (if any). I recommend learning how to use EXPLAIN because it is difficult for us to recommend an additional index. If I were to take a guess, I'd say that your timeback column requires an index, simply because a timestamp column is likely to be more varied than an owner column. Adding an index to every column will increase the time it takes to INSERT / UPDATE.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Focusing only on the query (and not the out-of-date mysql_ interface):

select *
from table_test 
WHERE ((owner = '$id' AND ownersecond = '0') OR ownersecond = '$id') AND
      catogory = '1' AND
      type = '$car[id]' AND
      caseB = '0' AND 
      timeback < TIMESTAMP(NOW()) OR timeback = '0000-00-00 00:00:00')

An appropriate index is on category, type, caseB, and then the other columns. So try this index:

create index idx_table_test_6 on table_test(category, type, caseB, timeback, owner, ownersecond)

The index can be used for all the where conditions, although the original data pages will be needed for the select *.

Upvotes: 1

Related Questions