Demiel: I ran it like this: mysqlcheck –all-databases –verbose

Demiel: Salle: ok, so what are my options then?

Clyatt: Zamba: Your options are: 1 Read the manual; 2 Hire someone who knows MySQL to maintain your server

Clyatt: Zamba: Meanwile if you can SELECT from thsi table dump it; drop it and reimport it again. If you can’t SELECT you better have a good backup

Demiel: Salle: i performed a successful mysqldump

Demiel: Salle: so i guess that means that it was successful?

Rumery: Check the data you got if it makes sense

Demiel: I’ll try importing it on a different mysql server

Demiel: And perform some selects on it instead

Demiel: Salle: will a successful ‘select count* from history’ mean that the table is in a state where i can drop the database and then recreate it from the mysqldump?

Demiel: Looks like it worked:

Demiel: 1 row in set 5 min 23.36 sec

Mantilia: Rumery, products.id cannot be null, it is primary key. I tried count1 out tho, no difference

Hoekman: I want to know how this could be improved upon. It seems to be simple, but I’m clueless. http://pastie.org/private/geemclti2kp8fcdh8ribq

Rumery: Betterton, you count all products which have matching campaign this way? Then I do not know how to make it faster than what you already have

Rumery: Betterton, is there any chance that a product will have a NOT NULL value in “products.campaign_id” but still no record for that campaign in “campaign_countries” ? = do you actually need the join or just existence of a campaign might be enough?

Abrantes: Rumery, Yes, that is what I am trying to do. It is currently very slow 5s. Is there some fundamental design mistake I have made?

Elks: Rumery, I just need existence of a campaign

Rumery: Betterton, you use InnoDB? InnoDB when counting rows has to work with transactions etc, so it has to actually count the rows which are “visible” in the current transaction, so it may take time when there are many of them – does it make sense for you to count “all” of them without any additional WHERE conditions like specific country or campaigns of specific type

Prahm: Myisam optimization only applies to SELECT COUNT* FROM table; query

Morlan: Rumery, campaign_countries is MyISAM and products is InnoDB

Rozar: Betterton: you can’t make that query faster

Germano: Do you want to SELECT COUNTDISTINCT products.id ?

Herek: Your query is counting product campaign countries

Weyrauch: I only want to count the products

Carlow: Which has a certain country in it’s campign

Keywan: Boleware, this is what I am doing at the moment: http://pastie.org/private/gkctsixipcjzir4ilksaeg

Gillespi: Betterton: but it is a different query

Streck: Explain it, not the one before

Rumery: Betterton, adding a WHERE clause may change the plan a lot – check it for this query – you will see a different order of tables probably

Noffsinger: Alright, I just tried to simplify it. I guess that what’s not to do :

Rumery: And as Boleware wrote – you currently count one product multiple times if the campaign has multiple countries ***ociated maybe it cannot happen now but you are not saying

Reddig: Rumery, it can happen, yes

Rumery: Betterton, yep, if you count everything, the results will be very different from counting some specific subset unless that subset makes a big part of your data

Wreede: Boleware, here is explain for the correct query: http://pastie.org/private/1danpyajslwepmcxfhs13w

Simkin: Have you got an index on campaign_countries.country_id ?

Clyatt: Betterton: It is much easier if you paste the EXPLAIN SELECT and result of the EXPLAIN togehter and also SHOW CREATE TABLE for both tables

Clyatt: Betterton: Are you sure you want to count the number of rows in the result of join?

Petosa: Boleware, I have a multi-col index including country_id/campaign_id