Prevent full table scan simple query - 【StackMirror】|php|mysql

Lately we been having a lot of problems with our mysql server i have been running some test with a profiler (JetProfiler) and a stresstester (loadUI). I have noticed that after having atleast 20 connections run with loudUI at same time it already makes it unable to connect or connect REALLY slow to the webpage. Jetprofiler says that prety much every query we run is bad since they all use full table scans, I'm wondering if this could actually be the problem.

Here's a screenshot of a query that probably gets run the most:

enter image description here

how would I improve this?

php
mysql

2012-04-04 07:50
by Holapress

Make sure you use indices on datum/webid columns - Minras 2012-04-04 07:53

Is it a rootserver? Or a normal webspace? And i think the question is better on ServerFault - René Höhle 2012-04-04 07:53

its a dedicated server, the server only runs our mysql database nothing els - Holapress 2012-04-04 07:53

You need to have an index for all the columns in the where clause (they must be added to the index in the order of the where clause, or you must reorder your where-clause, otherwise the btree-search doesn't work, resulting in a full table scan) - Thorsten Dittmar 2012-04-04 07:55

This looks like a case of totally misconfigured database server (default MySQL settings) and zero indexes on tables. I suggest getting someone who knows what they're doing to configure your server and add indices to your tables. 7k rows is nothing for a MySQL server, even if concurrency is slightly higher the query result would get cached. It is true that you lack indices, but it looks like that other things could be improved as well - N.B. 2012-04-04 08:46

Can you post the output from the EXPLAIN (the EXTRA part) - Salman A 2012-04-04 08:47

Just create single indices for following columns: datum, webid, and actief:

create index idx_datum on nieuws(datum);
create index idx_webid on nieuws(webid);
create index idx_actief on nieuws(actief);

This should yield in a better performance of this query.

2012-04-04 08:23
by Flo Doe

Index on actief is pointless, other two are fine - N.B. 2012-04-04 08:25

@N.B. why? actief is used inside where clause - Salman A 2012-04-04 08:49

@Salman A - low index cardinality - N.B. 2012-04-04 09:05

Low index cardinality is only appearant to native speakers of their language (actief - ja|nein) so I would suggest creating this index in general. Anyways in this case you are right with your remark - Flo Doe 2012-04-04 10:09