Help with proper index, or proper query?

Databases
1

The indexes for C and V only look at 1 row, but the index for M looks at ALL rows (but the PRIMARY is for ID_MEM). It says that PRIMARY is a possible key, but it isn’t using it.

The main content that I am returning is from C, yet it looks like the query might be reading this in an inefficient way.

SELECT V.ti, V.vti, V.ad, C.* , M.av, M.me, M.ID_MEM
FROM c_n AS C
JOIN vis AS V ON V.ID = C.viid
JOIN mes AS M ON M.ID_MEM = C.uid
WHERE (
(
C.aid =74410
AND C.sil =1
)
OR (
C.oai LIKE '%;;word;;%'
AND C.silm NOT LIKE '%;;word2;;%'
)
)
AND C.uid !=74410
AND C.uid >0
ORDER BY C.ID DESC
LIMIT 0 , 25

Help?

2

could you please do a SHOW CREATE TABLE for each table, and an EXPLAIN on the query

3

Thanks for the reply.

For security reasons, I have sent you a PM with that data.

4

had a look, but sorry, it’s too complicated for me

5

just a quick afterthought…

perhaps break the query up into two parts, eliminating the OR, and UNIONing them together –

SELECT...
  FROM ...
 WHERE C.aid =74410
   AND C.sil =1
   AND C.uid !=74410
   AND C.uid >0
UNION ALL
SELECT ...
  FROM ...
 WHERE C.oai LIKE '%;;word;;%'
   AND C.silm NOT LIKE '%;;word2;;%'
   AND C.uid !=74410
   AND C.uid >0
6

I played around with UNION, but I don’t think that is exactly what I want.

For now, I switched this over to 1 query with 2 tables, and then a query in the while() loop to grab the data from the third table. This results in 26 queries on the page, but it is much faster than just 1 bad query.

I think this can probably be optimized more. I’ll have to dig deeper.

Thanks for the help!

7

why not?

what i posted is exactly equivalent to what you posted